gordonje

-- what we want our distinct stores and the max number of wins for a person that played there
SELECT "AGENT NAME", max(count_person_wins), max(sum_person_winnings)
FROM (
        -- gets us the number of wins per person for each store
        SELECT 
                  "AGENT NAME"
                , "FIRSTNAME"
                , "LASTNAME"
                , "CITY"
                , "STATE"

gordonje

# django-calaccess-raw-data/calaccess_raw/__init__.py

def get_model_from_file_name(file_name):
    """
    Returns a calaccess_raw model to which the given .CSV or .TSV file maps
    """
    from django.apps import apps
    import re
    model_name = re.sub(r'\.\w+', '', file_name).title().replace('_', '')
    return apps.get_app_config("calaccess_raw").get_model(model_name)

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

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 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 '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 '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 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 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"