filer_id counts by table

filer_id_counts_by_table.sql

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
        LEFT JOIN temp_filer_ids b
        ON a."FILER_ID" = b.id
        WHERE b.id IS NULL
        UNION
        SELECT 'CVR_F470_CD' as table_name, COUNT(DISTINCT a."FILER_ID") as filer_id_count
        FROM "CVR_F470_CD" a
        LEFT JOIN temp_filer_ids b
        ON a."FILER_ID" = b.id
        WHERE b.id IS NULL
        UNION
        SELECT 'CVR_LOBBY_DISCLOSURE_CD' as table_name, COUNT(DISTINCT a."FILER_ID") as filer_id_count
        FROM "CVR_LOBBY_DISCLOSURE_CD" a
        LEFT JOIN temp_filer_ids b
        ON a."FILER_ID" = b.id
        WHERE b.id IS NULL
        UNION
        SELECT 'CVR_REGISTRATION_CD' as table_name, COUNT(DISTINCT a."FILER_ID") as filer_id_count
        FROM "CVR_REGISTRATION_CD" a
        LEFT JOIN temp_filer_ids b
        ON a."FILER_ID" = b.id
        WHERE b.id IS NULL
        UNION
        SELECT 'CVR_SO_CD' as table_name, COUNT(DISTINCT a."FILER_ID") as filer_id_count
        FROM "CVR_SO_CD" a
        LEFT JOIN temp_filer_ids b
        ON a."FILER_ID" = b.id
        WHERE b.id IS NULL
        UNION
        SELECT 'EFS_FILING_LOG_CD' as table_name, COUNT(DISTINCT a."FILER_ID") as filer_id_count
        FROM "EFS_FILING_LOG_CD" a
        LEFT JOIN temp_filer_ids b
        ON a."FILER_ID" = b.id
        WHERE b.id IS NULL
        UNION
        SELECT 'F501_502_CD' as table_name, COUNT(DISTINCT a."FILER_ID") as filer_id_count
        FROM "F501_502_CD" a
        LEFT JOIN temp_filer_ids b
        ON a."FILER_ID" = b.id
        WHERE b.id IS NULL
        UNION
        SELECT 'FILER_ACRONYMS_CD' as table_name, COUNT(DISTINCT a."FILER_ID") as filer_id_count
        FROM "FILER_ACRONYMS_CD" a
        LEFT JOIN temp_filer_ids b
        ON a."FILER_ID"::varchar = b.id
        WHERE b.id IS NULL
        UNION
        SELECT 'FILER_ADDRESS_CD' as table_name, COUNT(DISTINCT a."FILER_ID") as filer_id_count
        FROM "FILER_ADDRESS_CD" a
        LEFT JOIN temp_filer_ids b
        ON a."FILER_ID"::varchar = b.id
        WHERE b.id IS NULL
        UNION
        SELECT 'FILER_ETHICS_CLASS_CD' as table_name, COUNT(DISTINCT a."FILER_ID") as filer_id_count
        FROM "FILER_ETHICS_CLASS_CD" a
        LEFT JOIN temp_filer_ids b
        ON a."FILER_ID"::varchar = b.id
        WHERE b.id IS NULL
        UNION
        SELECT 'FILER_FILINGS_CD' as table_name, COUNT(DISTINCT a."FILER_ID") as filer_id_count
        FROM "FILER_FILINGS_CD" a
        LEFT JOIN temp_filer_ids b
        ON a."FILER_ID"::varchar = b.id
        WHERE b.id IS NULL
        UNION
        SELECT 'FILER_INTERESTS_CD' as table_name, COUNT(DISTINCT a."FILER_ID") as filer_id_count
        FROM "FILER_INTERESTS_CD" a
        LEFT JOIN temp_filer_ids b
        ON a."FILER_ID"::varchar = b.id
        WHERE b.id IS NULL
        UNION
        SELECT 'FILERNAME_CD' as table_name, COUNT(DISTINCT a."FILER_ID") as filer_id_count
        FROM "FILERNAME_CD" a
        LEFT JOIN temp_filer_ids b
        ON a."FILER_ID"::varchar = b.id
        WHERE b.id IS NULL
        UNION
        SELECT 'FILER_TO_FILER_TYPE_CD' as table_name, COUNT(DISTINCT a."FILER_ID") as filer_id_count
        FROM "FILER_TO_FILER_TYPE_CD" a
        LEFT JOIN temp_filer_ids b
        ON a."FILER_ID"::varchar = b.id
        WHERE b.id IS NULL
        UNION
        SELECT 'FILER_XREF_CD' as table_name, COUNT(DISTINCT a."FILER_ID") as filer_id_count
        FROM "FILER_XREF_CD" a
        LEFT JOIN temp_filer_ids b
        ON a."FILER_ID"::varchar = b.id
        WHERE b.id IS NULL
        UNION
        SELECT 'LOBBYING_CHG_LOG_CD' as table_name, COUNT(DISTINCT a."FILER_ID") as filer_id_count
        FROM "LOBBYING_CHG_LOG_CD" a
        LEFT JOIN temp_filer_ids b
        ON a."FILER_ID"::varchar = b.id
        WHERE b.id IS NULL
        UNION
        SELECT 'LOBBYIST_CONTRIBUTIONS1_CD' as table_name, COUNT(DISTINCT a."FILER_ID") as filer_id_count
        FROM "LOBBYIST_CONTRIBUTIONS1_CD" a
        LEFT JOIN temp_filer_ids b
        ON a."FILER_ID"::varchar = b.id
        WHERE b.id IS NULL
        UNION
        SELECT 'LOBBYIST_CONTRIBUTIONS2_CD' as table_name, COUNT(DISTINCT a."FILER_ID") as filer_id_count
        FROM "LOBBYIST_CONTRIBUTIONS2_CD" a
        LEFT JOIN temp_filer_ids b
        ON a."FILER_ID"::varchar = b.id
        WHERE b.id IS NULL
        UNION
        SELECT 'LOBBYIST_CONTRIBUTIONS3_CD' as table_name, COUNT(DISTINCT a."FILER_ID") as filer_id_count
        FROM "LOBBYIST_CONTRIBUTIONS3_CD" a
        LEFT JOIN temp_filer_ids b
        ON a."FILER_ID"::varchar = b.id
        WHERE b.id IS NULL
        UNION
        SELECT 'RECEIVED_FILINGS_CD' as table_name, COUNT(DISTINCT a."FILER_ID") as filer_id_count
        FROM "RECEIVED_FILINGS_CD" a
        LEFT JOIN temp_filer_ids b
        ON a."FILER_ID"::varchar = b.id
        WHERE b.id IS NULL
) as foo
WHERE foo.filer_id_count > 0
ORDER BY foo.filer_id_count DESC;