gordonje

select link_group_id, link_type_description, count(*)
from calaccess_processed_candidate_committees a
join (
    select cand_filer_id, committee_filer_id, count(*)
    from calaccess_processed_candidate_committees
    group by 1, 2
    having count(*) > 1
) as dupes
on a.cand_filer_id = dupes.cand_filer_id
and a.committee_filer_id = dupes.committee_filer_id

gordonje

CREATE TABLE calaccess_processed_candidate_committees AS
SELECT   
        @link_type as link_group_id,
        lu."CODE_DESC" AS link_type_description,
        cand_filer_id,
        committee_filer_id,
        MIN(session) AS first_session,
        MAX(session) AS last_session,
        MIN(effective_date) AS first_effective_date,
        MAX(effective_date) AS last_effective_date,

gordonje

SELECT 
        @link_type as link_group, 
        cand_filer_id,
        other_filer_id,
        COUNT(DISTINCT other_filer_type) as filer_type_count
FROM (
        SELECT 
                links."FILER_ID_A" AS cand_filer_id,
                links."FILER_ID_B" AS other_filer_id,
                "LINK_TYPE" as link_type,

gordonje

SELECT 
        a_types."DESCRIPTION" AS filer_a_type,
        b_types."DESCRIPTION" AS filer_b_type,
        COUNT(DISTINCT links.id)
FROM "FILER_LINKS_CD" links
JOIN (
        SELECT "FILER_ID", "DESCRIPTION"
        FROM "FILER_TO_FILER_TYPE_CD" f2ft
        JOIN "FILER_TYPES_CD" ft
        ON f2ft."FILER_TYPE" = ft."FILER_TYPE"

gordonje

SELECT 
    'A is a CANDIDATE/OFFICEHOLDER' AS scenario,
     "LINK_TYPE",
    COUNT(DISTINCT A."FILER_ID_A") AS the_count
FROM "FILER_LINKS_CD" A
JOIN "FILER_TO_FILER_TYPE_CD" B
ON A."FILER_ID_A" = B."FILER_ID"
AND B."FILER_TYPE" = 8 -- code for CANDIDATE/OFFICEHOLDER
WHERE "LINK_TYPE" = 12011
GROUP BY 1, 2

gordonje

SELECT table_name, filing_id_count
FROM (
    SELECT 'CVR2_CAMPAIGN_DISCLOSURE_CD' as table_name, COUNT(DISTINCT a."FILING_ID") as filing_id_count
    FROM "CVR2_CAMPAIGN_DISCLOSURE_CD" a
    LEFT JOIN "FILINGS_CD" b
    ON a."FILING_ID" = b."FILING_ID"
    WHERE b."FILING_ID" IS NULL
    UNION
    SELECT 'CVR2_LOBBY_DISCLOSURE_CD' as table_name, COUNT(DISTINCT a."FILING_ID") as filing_id_count
    FROM "CVR2_LOBBY_DISCLOSURE_CD" a

gordonje

SELECT foo.table_name, foo.filer_id_count
FROM (
        SELECT 'BALLOT_MEASURES_CD' as table_name, COUNT(DISTINCT a."FILER_ID") as filer_id_count
        FROM "BALLOT_MEASURES_CD" a
        LEFT JOIN temp_filer_ids b
        ON a."FILER_ID"::varchar = b.id
        WHERE b.id IS NULL
        UNION
        SELECT 'CVR_CAMPAIGN_DISCLOSURE_CD' as table_name, COUNT(DISTINCT a."FILER_ID") as filer_id_count
        FROM "CVR_CAMPAIGN_DISCLOSURE_CD" a

gordonje

-- 1,471 uncaptured records
SELECT COUNT(DISTINCT cvr."FILER_ID")
FROM "CVR_CAMPAIGN_DISCLOSURE_CD" cvr
JOIN f460_summary f460
ON cvr."FILING_ID" = f460.filing_id
AND cvr."AMEND_ID" = f460.amend_id
JOIN "FILER_XREF_CD" x
ON x."XREF_ID" = cvr."FILER_ID"
LEFT JOIN (
        SELECT "FILER_ID_A"::varchar as filer_id

gordonje

-- cases where there's a contribution total on the summary sheet, but not the schedule
select summ.filing_id, summ.amend_id, summ.monetary_contributions, a.combined_total
from f460_summary summ
join f460_schedule_a a
on summ.filing_id = a.filing_id
and summ.amend_id = a.amend_id
where summ.monetary_contributions > 0 and (a.combined_total is null or a.combined_total = 0)
order by 1 desc, 2;

-- cases where there's a contribution total on the schedule but not the summary sheet

gordonje

-- Monetary Contributions Received
CREATE TABLE f460_schedule_a AS
SELECT 
    cvr."FILING_ID" as filing_id,
    cvr."AMEND_ID" as amend_id,
    itemd."AMOUNT_A" as itemized_total,
    unitemd."AMOUNT_A" as unitemized_total,
    combined."AMOUNT_A" as combined_total
FROM "CVR_CAMPAIGN_DISCLOSURE_CD" cvr
-- get the itemized total