Counts of distinct FILER_IDs not in FILERS_CD

filer_ids_not_in_filers_cd.sql

SELECT foo.table_name, cols.data_type, 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 "FILERS_CD" b
        ON a."FILER_ID" = b."FILER_ID"
        WHERE b."FILER_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 "FILERS_CD" b
        ON a."FILER_ID" = b."FILER_ID"::varchar
        WHERE b."FILER_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 "FILERS_CD" b
        ON a."FILER_ID" = b."FILER_ID"::varchar
        WHERE b."FILER_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 "FILERS_CD" b
        ON a."FILER_ID" = b."FILER_ID"::varchar
        WHERE b."FILER_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 "FILERS_CD" b
        ON a."FILER_ID" = b."FILER_ID"::varchar
        WHERE b."FILER_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 "FILERS_CD" b
        ON a."FILER_ID" = b."FILER_ID"::varchar
        WHERE b."FILER_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 "FILERS_CD" b
        ON a."FILER_ID" = b."FILER_ID"::varchar
        WHERE b."FILER_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 "FILERS_CD" b
        ON a."FILER_ID" = b."FILER_ID"::varchar
        WHERE b."FILER_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 "FILERS_CD" b
        ON a."FILER_ID" = b."FILER_ID"
        WHERE b."FILER_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 "FILERS_CD" b
        ON a."FILER_ID" = b."FILER_ID"
        WHERE b."FILER_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 "FILERS_CD" b
        ON a."FILER_ID" = b."FILER_ID"
        WHERE b."FILER_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 "FILERS_CD" b
        ON a."FILER_ID" = b."FILER_ID"
        WHERE b."FILER_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 "FILERS_CD" b
        ON a."FILER_ID" = b."FILER_ID"
        WHERE b."FILER_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 "FILERS_CD" b
        ON a."FILER_ID" = b."FILER_ID"
        WHERE b."FILER_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 "FILERS_CD" b
        ON a."FILER_ID" = b."FILER_ID"
        WHERE b."FILER_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 "FILERS_CD" b
        ON a."FILER_ID" = b."FILER_ID"
        WHERE b."FILER_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 "FILERS_CD" b
        ON a."FILER_ID" = b."FILER_ID"
        WHERE b."FILER_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 "FILERS_CD" b
        ON a."FILER_ID" = b."FILER_ID"
        WHERE b."FILER_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 "FILERS_CD" b
        ON a."FILER_ID" = b."FILER_ID"
        WHERE b."FILER_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 "FILERS_CD" b
        ON a."FILER_ID" = b."FILER_ID"
        WHERE b."FILER_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 "FILERS_CD" b
        ON a."FILER_ID" = b."FILER_ID"
        WHERE b."FILER_ID" IS NULL
) as foo
JOIN information_schema.columns cols
ON foo.table_name = cols.table_name
AND cols.column_name = 'FILER_ID'
WHERE foo.filer_id_count > 0
ORDER BY foo.filer_id_count DESC;

results.txt

table_name                  data_type          filer_id_count  
--------------------------  -----------------  --------------  
EFS_FILING_LOG_CD           character varying  4818            
F501_502_CD                 character varying  4494            
CVR_LOBBY_DISCLOSURE_CD     character varying  3614            
CVR_CAMPAIGN_DISCLOSURE_CD  character varying  2703            
CVR_REGISTRATION_CD         character varying  2693            
CVR_SO_CD                   character varying  1000            
LOBBYING_CHG_LOG_CD         integer            199             
RECEIVED_FILINGS_CD         integer            132             
LOBBYIST_CONTRIBUTIONS3_CD  integer            2               
LOBBYIST_CONTRIBUTIONS2_CD  integer            2               
LOBBYIST_CONTRIBUTIONS1_CD  integer            2