gordonje’s gists

gordonje / check_for_non_zero_b_or_c_amounts.sql

Created September 8, 2016 22:58

check for non-zero AMOUNT_B or AMOUNT_C values

	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);

gordonje / filing_ids_and_amend_missing_cvr.sql

Created September 8, 2016 21:38

filing_ids and amendments missing cover sheets

	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

gordonje / non_numeric_xref_ids.sql

Created August 31, 2016 16:00

xref_ids with non-numeric characters

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

gordonje / filing_ids_not_in_filings_cd.sql

Created August 29, 2016 23:47

FILING_ID values not in FILINGS_CD

	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 / total_distinct_filing_ids.sql

Last active August 29, 2016 23:13

Total distinct FILING_ID values

	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 / count_filing_ids_by_table.sql

Last active August 29, 2016 22:56

counts of distinct FILING_IDs (by table)

	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 / total_distinct_filing_ids.sql

Last active August 29, 2016 22:56

total distinct FILING_ID values

	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 / filer_ids_not_in_filers_cd.sql

Last active August 29, 2016 19:36

Counts of distinct FILER_IDs not in FILERS_CD

	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 / total_distinct_filer_ids.sql

Last active August 29, 2016 23:08

Total distinct FILER_ID values

gordonje / count_filer_ids_by_table.sql

Last active August 29, 2016 22:58

counts of distinct FILER_IDS (by table)

	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

James Gordon gordonje