This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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 |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
SELECT COUNT(DISTINCT "XREF_ID") | |
FROM "FILER_XREF_CD" | |
WHERE "XREF_ID" ~ '\D'; |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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 |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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); |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
-- 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 |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
-- 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 |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
-- 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 |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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 |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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 |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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 |