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

gordonje

SELECT *
FROM "CVR_CAMPAIGN_DISCLOSURE_CD" a
JOIN "SMRY_CD" b
ON a."FILING_ID" = b."FILING_ID"
AND a."AMEND_ID" = b."AMEND_ID"
WHERE a."FORM_TYPE" = 'F460'
AND UPPER(b."FORM_TYPE") ~ '^[A-Z]$'
AND ("AMOUNT_B" > 0 OR "AMOUNT_C" > 0);

gordonje

SELECT a."FILING_ID", a."AMEND_ID", COUNT(*)
FROM "SMRY_CD" a
LEFT JOIN "CVR_CAMPAIGN_DISCLOSURE_CD" b
ON a."FILING_ID" = b."FILING_ID"
AND a."AMEND_ID" = b."AMEND_ID"
WHERE b."FILING_ID" IS NULL
AND a."FORM_TYPE" IN (
        -- get the possible SMRY_CD form_type values for the F460 filings in CVR_CAMPAIGN_DISCLOSURE_CD
        SELECT DISTINCT a."FORM_TYPE"
        FROM "SMRY_CD" a