CREATE TABLE qoz_vote_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)

       , by_candidate AS (SELECT lpad(county_fips::text
                                    , 5
                                    , '0') AS county_id
                               , sum(CASE
                                       WHEN (candidate_normalized = 'trump')
                                         THEN votes
                                       ELSE 0
                                     END) AS trump_votes
                               , sum(CASE
                                       WHEN (candidate_normalized = 'clinton')
                                         THEN votes
                                       ELSE 0
                                     END) AS clinton_votes
                               , sum(votes) AS total_votes
                          FROM "splitgraph/2016_election:3835145ada3f07cad99087d1b1071122d58c48783cbfe4694c101d35651fba90".precinct_results
                          GROUP BY county_id)

       SELECT v.county_id
            , COALESCE((q.qoz_tract_count)::numeric / (t.total_tract_count)::numeric
                     , 0.0) AS qoz_tract_fraction
            , trump_votes / total_votes AS trump_vote_fraction
            , clinton_votes / total_votes AS clinton_vote_fraction
            , total_votes
       FROM by_candidate AS v
            LEFT JOIN county_tracts AS t ON t.county_id = v.county_id
            LEFT JOIN qoz_tracts AS q ON t.county_id = q.county_id