CREATE TABLE county_votes
  AS WITH votes_by_county AS (SELECT state_postal
                                   , pg_catalog.substring(lpad(county_fips::text
                                                             , 5
                                                             , '0')
                                                        , 3
                                                        , 6) AS county_fips_short
                                   , 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 state_postal, county_fips_short)

       , us_counties AS (SELECT c.admin1_code AS state
                              , c.admin2_code AS county_fips
                              , c.name AS county_name
                              , c.population
                         FROM "splitgraph/geonames:0b77a102cbabe2de8597dcab71f1c333f16f9e1963c9c8982988e10e65989c62".all_countries AS c
                         WHERE (c.feature_class = 'A')
                           AND (c.feature_code = 'ADM2')
                           AND (c.country_code = 'US'))

       SELECT v.state_postal
            , county_name
            , v.county_fips_short
            , trump_votes
            , clinton_votes
            , total_votes
            , population
       FROM us_counties AS c
            INNER JOIN votes_by_county AS v ON (v.state_postal = c.state)
                                           AND (v.county_fips_short = c.county_fips)