mildbyte/qoz_vote_fraction

Provenance

3 Dependencies
 
SQL
2 Sources
CREATE TABLE qoz_tract_fraction
  AS WITH county_tracts AS (SELECT count(*) AS total_tract_count
                                 , pg_catalog.substring(lpad("TractId"::text
                                                           , 11
                                                           , '0')
                                                      , 0
                                                      , 6) AS county_id
                            FROM "splitgraph/census:17a596cf4642f50713a817dcd34677cec329e3a83b884a38e115389ecc7e1f0e".acs2017_census_tract_data
                            GROUP BY county_id)

       , qoz_tracts AS (SELECT count(*) AS qoz_tract_count
                             , pg_catalog.substring(lpad("Census Tract Number"::text
                                                       , 11
                                                       , '0')
                                                  , 0
                                                  , 6) AS county_id
                        FROM "splitgraph/qoz:5818ea35bf99c1f93fb3fb4f751c69b93db1e6a1354b2fc558f8a0036041588a".qoz
                        GROUP BY county_id)

       SELECT c.county_id AS county_id
            , COALESCE((q.qoz_tract_count)::numeric
                     , 0.0) / total_tract_count AS qoz_tract_fraction
       FROM qoz_tracts AS q
            INNER JOIN county_tracts AS c ON q.county_id = c.county_id
 
1 Table
  • IMPORT INTO vote_fraction
    SELECT lpad(county_fips::text
              , 5
              , '0') AS county_id
         , sum(CASE
                 WHEN ((candidate_normalized = 'trump'))
                   THEN votes
                 ELSE 0
               END) / sum(votes) AS trump_vote_fraction
         , sum(CASE
                 WHEN ((candidate_normalized = 'clinton'))
                   THEN votes
                 ELSE 0
               END) / sum(votes) AS clinton_vote_fraction
         , sum(votes) AS total_votes
    FROM precinct_results
    GROUP BY county_id
 
SQL
0 Sources
CREATE TABLE qoz_vote_fraction
  AS SELECT v.county_id
          , COALESCE(qoz_tract_fraction, 0)
          , trump_vote_fraction
          , clinton_vote_fraction
          , total_votes
     FROM vote_fraction AS v
          LEFT JOIN qoz_tract_fraction AS q ON q.county_id = v.county_id
     WHERE v.county_id IS NOT NULL;

ALTER TABLE qoz_vote_fraction ADD PRIMARY KEY (county_id)