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_id1 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)