filing_id counts by table

filing_id_counts_by_table.sql

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
    LEFT JOIN "FILINGS_CD" b
    ON a."FILING_ID" = b."FILING_ID"
    WHERE b."FILING_ID" IS NULL
    UNION
    SELECT 'CVR2_REGISTRATION_CD' as table_name, COUNT(DISTINCT a."FILING_ID") as filing_id_count
    FROM "CVR2_REGISTRATION_CD" a
    LEFT JOIN "FILINGS_CD" b
    ON a."FILING_ID" = b."FILING_ID"
    WHERE b."FILING_ID" IS NULL
    UNION
    SELECT 'CVR2_SO_CD' as table_name, COUNT(DISTINCT a."FILING_ID") as filing_id_count
    FROM "CVR2_SO_CD" a
    LEFT JOIN "FILINGS_CD" b
    ON a."FILING_ID" = b."FILING_ID"
    WHERE b."FILING_ID" IS NULL
    UNION
    SELECT 'CVR3_VERIFICATION_INFO_CD' as table_name, COUNT(DISTINCT a."FILING_ID") as filing_id_count
    FROM "CVR3_VERIFICATION_INFO_CD" a
    LEFT JOIN "FILINGS_CD" b
    ON a."FILING_ID" = b."FILING_ID"
    WHERE b."FILING_ID" IS NULL
    UNION
    SELECT 'CVR_CAMPAIGN_DISCLOSURE_CD' as table_name, COUNT(DISTINCT a."FILING_ID") as filing_id_count
    FROM "CVR_CAMPAIGN_DISCLOSURE_CD" a
    LEFT JOIN "FILINGS_CD" b
    ON a."FILING_ID" = b."FILING_ID"
    WHERE b."FILING_ID" IS NULL
    UNION
    SELECT 'CVR_E530_CD' as table_name, COUNT(DISTINCT a."FILING_ID") as filing_id_count
    FROM "CVR_E530_CD" a
    LEFT JOIN "FILINGS_CD" b
    ON a."FILING_ID" = b."FILING_ID"
    WHERE b."FILING_ID" IS NULL
    UNION
    SELECT 'CVR_F470_CD' as table_name, COUNT(DISTINCT a."FILING_ID") as filing_id_count
    FROM "CVR_F470_CD" a
    LEFT JOIN "FILINGS_CD" b
    ON a."FILING_ID" = b."FILING_ID"
    WHERE b."FILING_ID" IS NULL
    UNION
    SELECT 'CVR_LOBBY_DISCLOSURE_CD' as table_name, COUNT(DISTINCT a."FILING_ID") as filing_id_count
    FROM "CVR_LOBBY_DISCLOSURE_CD" a
    LEFT JOIN "FILINGS_CD" b
    ON a."FILING_ID" = b."FILING_ID"
    WHERE b."FILING_ID" IS NULL
    UNION
    SELECT 'CVR_REGISTRATION_CD' as table_name, COUNT(DISTINCT a."FILING_ID") as filing_id_count
    FROM "CVR_REGISTRATION_CD" a
    LEFT JOIN "FILINGS_CD" b
    ON a."FILING_ID" = b."FILING_ID"
    WHERE b."FILING_ID" IS NULL
    UNION
    SELECT 'CVR_SO_CD' as table_name, COUNT(DISTINCT a."FILING_ID") as filing_id_count
    FROM "CVR_SO_CD" a
    LEFT JOIN "FILINGS_CD" b
    ON a."FILING_ID" = b."FILING_ID"
    WHERE b."FILING_ID" IS NULL
    UNION
    SELECT 'DEBT_CD' as table_name, COUNT(DISTINCT a."FILING_ID") as filing_id_count
    FROM "DEBT_CD" a
    LEFT JOIN "FILINGS_CD" b
    ON a."FILING_ID" = b."FILING_ID"
    WHERE b."FILING_ID" IS NULL
    UNION
    SELECT 'EXPN_CD' as table_name, COUNT(DISTINCT a."FILING_ID") as filing_id_count
    FROM "EXPN_CD" a
    LEFT JOIN "FILINGS_CD" b
    ON a."FILING_ID" = b."FILING_ID"
    WHERE b."FILING_ID" IS NULL
    UNION
    SELECT 'F495P2_CD' as table_name, COUNT(DISTINCT a."FILING_ID") as filing_id_count
    FROM "F495P2_CD" a
    LEFT JOIN "FILINGS_CD" b
    ON a."FILING_ID" = b."FILING_ID"
    WHERE b."FILING_ID" IS NULL
    UNION
    SELECT 'F501_502_CD' as table_name, COUNT(DISTINCT a."FILING_ID") as filing_id_count
    FROM "F501_502_CD" a
    LEFT JOIN "FILINGS_CD" b
    ON a."FILING_ID" = b."FILING_ID"
    WHERE b."FILING_ID" IS NULL
    UNION
    SELECT 'F690P2_CD' as table_name, COUNT(DISTINCT a."FILING_ID") as filing_id_count
    FROM "F690P2_CD" a
    LEFT JOIN "FILINGS_CD" b
    ON a."FILING_ID" = b."FILING_ID"
    WHERE b."FILING_ID" IS NULL
    UNION
    SELECT 'FILER_FILINGS_CD' as table_name, COUNT(DISTINCT a."FILING_ID") as filing_id_count
    FROM "FILER_FILINGS_CD" a
    LEFT JOIN "FILINGS_CD" b
    ON a."FILING_ID" = b."FILING_ID"
    WHERE b."FILING_ID" IS NULL
    UNION
    SELECT 'HDR_CD' as table_name, COUNT(DISTINCT a."FILING_ID") as filing_id_count
    FROM "HDR_CD" a
    LEFT JOIN "FILINGS_CD" b
    ON a."FILING_ID" = b."FILING_ID"
    WHERE b."FILING_ID" IS NULL
    UNION
    SELECT 'LATT_CD' as table_name, COUNT(DISTINCT a."FILING_ID") as filing_id_count
    FROM "LATT_CD" a
    LEFT JOIN "FILINGS_CD" b
    ON a."FILING_ID" = b."FILING_ID"
    WHERE b."FILING_ID" IS NULL
    UNION
    SELECT 'LCCM_CD' as table_name, COUNT(DISTINCT a."FILING_ID") as filing_id_count
    FROM "LCCM_CD" a
    LEFT JOIN "FILINGS_CD" b
    ON a."FILING_ID" = b."FILING_ID"
    WHERE b."FILING_ID" IS NULL
    UNION
    SELECT 'LEMP_CD' as table_name, COUNT(DISTINCT a."FILING_ID") as filing_id_count
    FROM "LEMP_CD" a
    LEFT JOIN "FILINGS_CD" b
    ON a."FILING_ID" = b."FILING_ID"
    WHERE b."FILING_ID" IS NULL
    UNION
    SELECT 'LEXP_CD' as table_name, COUNT(DISTINCT a."FILING_ID") as filing_id_count
    FROM "LEXP_CD" a
    LEFT JOIN "FILINGS_CD" b
    ON a."FILING_ID" = b."FILING_ID"
    WHERE b."FILING_ID" IS NULL
    UNION
    SELECT 'LOAN_CD' as table_name, COUNT(DISTINCT a."FILING_ID") as filing_id_count
    FROM "LOAN_CD" a
    LEFT JOIN "FILINGS_CD" b
    ON a."FILING_ID" = b."FILING_ID"
    WHERE b."FILING_ID" IS NULL
    UNION
    SELECT 'LOBBY_AMENDMENTS_CD' as table_name, COUNT(DISTINCT a."FILING_ID") as filing_id_count
    FROM "LOBBY_AMENDMENTS_CD" a
    LEFT JOIN "FILINGS_CD" b
    ON a."FILING_ID" = b."FILING_ID"
    WHERE b."FILING_ID" IS NULL
    UNION
    SELECT 'LOBBYIST_FIRM_EMPLOYER1_CD' as table_name, COUNT(DISTINCT a."FILING_ID") as filing_id_count
    FROM "LOBBYIST_FIRM_EMPLOYER1_CD" a
    LEFT JOIN "FILINGS_CD" b
    ON a."FILING_ID" = b."FILING_ID"
    WHERE b."FILING_ID" IS NULL
    UNION
    SELECT 'LOBBYIST_FIRM_EMPLOYER2_CD' as table_name, COUNT(DISTINCT a."FILING_ID") as filing_id_count
    FROM "LOBBYIST_FIRM_EMPLOYER2_CD" a
    LEFT JOIN "FILINGS_CD" b
    ON a."FILING_ID" = b."FILING_ID"
    WHERE b."FILING_ID" IS NULL
    UNION
    SELECT 'LOTH_CD' as table_name, COUNT(DISTINCT a."FILING_ID") as filing_id_count
    FROM "LOTH_CD" a
    LEFT JOIN "FILINGS_CD" b
    ON a."FILING_ID" = b."FILING_ID"
    WHERE b."FILING_ID" IS NULL
    UNION
    SELECT 'LPAY_CD' as table_name, COUNT(DISTINCT a."FILING_ID") as filing_id_count
    FROM "LPAY_CD" a
    LEFT JOIN "FILINGS_CD" b
    ON a."FILING_ID" = b."FILING_ID"
    WHERE b."FILING_ID" IS NULL
    UNION
    SELECT 'RCPT_CD' as table_name, COUNT(DISTINCT a."FILING_ID") as filing_id_count
    FROM "RCPT_CD" a
    LEFT JOIN "FILINGS_CD" b
    ON a."FILING_ID" = b."FILING_ID"
    WHERE b."FILING_ID" IS NULL
    UNION
    SELECT 'RECEIVED_FILINGS_CD' as table_name, COUNT(DISTINCT a."FILING_ID") as filing_id_count
    FROM "RECEIVED_FILINGS_CD" a
    LEFT JOIN "FILINGS_CD" b
    ON a."FILING_ID" = b."FILING_ID"
    WHERE b."FILING_ID" IS NULL
    UNION
    SELECT 'S401_CD' as table_name, COUNT(DISTINCT a."FILING_ID") as filing_id_count
    FROM "S401_CD" a
    LEFT JOIN "FILINGS_CD" b
    ON a."FILING_ID" = b."FILING_ID"
    WHERE b."FILING_ID" IS NULL
    UNION
    SELECT 'S496_CD' as table_name, COUNT(DISTINCT a."FILING_ID") as filing_id_count
    FROM "S496_CD" a
    LEFT JOIN "FILINGS_CD" b
    ON a."FILING_ID" = b."FILING_ID"
    WHERE b."FILING_ID" IS NULL
    UNION
    SELECT 'S497_CD' as table_name, COUNT(DISTINCT a."FILING_ID") as filing_id_count
    FROM "S497_CD" a
    LEFT JOIN "FILINGS_CD" b
    ON a."FILING_ID" = b."FILING_ID"
    WHERE b."FILING_ID" IS NULL
    UNION
    SELECT 'S498_CD' as table_name, COUNT(DISTINCT a."FILING_ID") as filing_id_count
    FROM "S498_CD" a
    LEFT JOIN "FILINGS_CD" b
    ON a."FILING_ID" = b."FILING_ID"
    WHERE b."FILING_ID" IS NULL
    UNION
    SELECT 'SMRY_CD' as table_name, COUNT(DISTINCT a."FILING_ID") as filing_id_count
    FROM "SMRY_CD" a
    LEFT JOIN "FILINGS_CD" b
    ON a."FILING_ID" = b."FILING_ID"
    WHERE b."FILING_ID" IS NULL
    UNION
    SELECT 'SPLT_CD' as table_name, COUNT(DISTINCT a."FILING_ID") as filing_id_count
    FROM "SPLT_CD" a
    LEFT JOIN "FILINGS_CD" b
    ON a."FILING_ID" = b."FILING_ID"
    WHERE b."FILING_ID" IS NULL
    UNION
    SELECT 'TEXT_MEMO_CD' as table_name, COUNT(DISTINCT a."FILING_ID") as filing_id_count
    FROM "TEXT_MEMO_CD" a
    LEFT JOIN "FILINGS_CD" b
    ON a."FILING_ID" = b."FILING_ID"
    WHERE b."FILING_ID" IS NULL
) as foo
WHERE foo.filer_id_count > 0
ORDER BY foo.filer_id_count DESC;