gordonje

SELECT COUNT(DISTINCT "XREF_ID")
FROM "FILER_XREF_CD"
WHERE "XREF_ID" ~ '\D';

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 COUNT(DISTINCT "FILING_ID")
FROM (
    SELECT "FILING_ID"
    FROM "CVR2_CAMPAIGN_DISCLOSURE_CD"
    UNION
    SELECT "FILING_ID"
    FROM "CVR2_LOBBY_DISCLOSURE_CD"
    UNION
    SELECT "FILING_ID"
    FROM "CVR2_REGISTRATION_CD"

gordonje

SELECT 'CVR2_CAMPAIGN_DISCLOSURE_CD' as table_name, COUNT(DISTINCT "FILING_ID") as filing_id_count
FROM "CVR2_CAMPAIGN_DISCLOSURE_CD"
UNION
SELECT 'CVR2_LOBBY_DISCLOSURE_CD' as table_name, COUNT(DISTINCT "FILING_ID") as filing_id_count
FROM "CVR2_LOBBY_DISCLOSURE_CD"
UNION
SELECT 'CVR2_REGISTRATION_CD' as table_name, COUNT(DISTINCT "FILING_ID") as filing_id_count
FROM "CVR2_REGISTRATION_CD"
UNION
SELECT 'CVR2_SO_CD' as table_name, COUNT(DISTINCT "FILING_ID") as filing_id_count

gordonje

SELECT COUNT(DISTINCT "FILING_ID")
FROM (
    SELECT "FILING_ID"
    FROM "CVR2_CAMPAIGN_DISCLOSURE_CD"
    UNION
    SELECT "FILING_ID"
    FROM "CVR2_LOBBY_DISCLOSURE_CD"
    UNION
    SELECT "FILING_ID"
    FROM "CVR2_REGISTRATION_CD"

gordonje

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

gordonje

SELECT COUNT(*)
FROM (
    SELECT "FILER_ID"::varchar
    FROM "FILER_XREF_CD"
    UNION
    SELECT "FILER_ID"::varchar
    FROM "FILER_FILINGS_CD"
    UNION
    SELECT "FILER_ID"::varchar
    FROM "FILERNAME_CD"

gordonje

SELECT 'BALLOT_MEASURES_CD' as table_name, COUNT(DISTINCT "FILER_ID"::varchar) as filer_id_count
FROM "BALLOT_MEASURES_CD"
UNION
SELECT 'CVR_CAMPAIGN_DISCLOSURE_CD' as table_name, COUNT(DISTINCT "FILER_ID") as filer_id_count
FROM "CVR_CAMPAIGN_DISCLOSURE_CD"
UNION
SELECT 'CVR_F470_CD' as table_name, COUNT(DISTINCT "FILER_ID") as filer_id_count
FROM "CVR_F470_CD"
UNION
SELECT 'CVR_LOBBY_DISCLOSURE_CD' as table_name, COUNT(DISTINCT "FILER_ID") as filer_id_count

gordonje

select 
        date_part('month', a."RPT_DATE") as month,
        date_part('year', a."RPT_DATE") as year,
        COUNT(*) as the_count
from "F501_502_CD" a
join "FILER_FILINGS_CD" b
on a."FILING_ID" =  b."FILING_ID"
where a."YR_OF_ELEC" = 2016
and a."FORM_TYPE" = 'F501'
group by 1, 2

gordonje

SELECT date_part('year', "DEADLINE") as deadline_year, COUNT(*)
FROM "FILER_FILINGS_CD" as a
JOIN "FILING_PERIOD_CD" as b
ON a."PERIOD_ID" = b."PERIOD_ID"
WHERE "FILING_ID" in (
  SELECT "FILING_ID"
  FROM "CVR2_CAMPAIGN_DISCLOSURE_CD"
  WHERE upper("F460_PART") in  ('4A', '4B')
)
GROUP BY 1