SQL {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}
FROM splitgraph/2016_election:3835145ada3f07cad99087d1b1071122d58c48783cbfe4694c101d35651fba90 IMPORT {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} AS vote_fraction
SQL {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)}