District rollup examples
We know that rolling up district information can be a bit tricky. Below are two sample queries: the first works with shape files and the second utilizes the
crosswalk_district_block_2020 table. Don't hesitate to reach out to us if you have any questions! WITH
blocks AS (
SELECT
block_fips_2020,
COUNT(DISTINCT targetsmart_voterbase_id) AS total_voters,
SUM(gender_female) AS female,
SUM(gender_male) AS male,
SUM(gender_nonbinary) AS nonbinary,
ROUND(AVG(age),2) AS age,
SUM(turnout22p) AS turnout_22_p,
SUM(turnout22g) AS turnout_22_g,
SUM(turnout20p) AS turnout_20_p,
SUM(turnout20g) AS turnout_20_g,
SUM(turnout16p) AS turnout_16_p,
SUM(turnout16g) AS turnout_16_g
FROM
`deck-236019.hubble_voter.deck_person`
WHERE
reg_address_state = 'MT'
GROUP BY
block_fips_2020),
block_shapes AS (
SELECT
block_fips_2020,
shape AS block_shape
FROM
`deck-236019.hubble_geography.block_2020`
WHERE
state_abbreviation = 'MT'),
districts AS (
SELECT
jurisdiction_state,
jurisdiction_type,
jurisdiction_name,
type AS office_type,
office_name,
name AS office_number,
shape AS district_shape,
district_id
FROM
`deck-236019.hubble_geography.district`
WHERE
jurisdiction_state = 'MT'
AND office_name = 'State House')
SELECT
district_id,
office_number AS office,
SUM(total_voters) AS total_voters,
ROUND(SUM(female)/SUM(total_voters),3) AS pct_female,
ROUND(SUM(male)/SUM(total_voters),3) AS pct_male,
ROUND(SUM(nonbinary)/SUM(total_voters),3) AS pct_nonbinary,
ROUND(AVG(age),2) AS avg_age,
SUM(turnout_22_p) AS turnout_22_p,
SUM(turnout_22_g) AS turnout_22_g,
SUM(turnout_20_p) AS turnout_20_p,
SUM(turnout_20_g) AS turnout_20_g,
SUM(turnout_16_p) AS turnout_16_p,
SUM(turnout_16_g) AS turnout_16_g
FROM
blocks,
block_shapes,
districts
WHERE
ST_AREA(ST_INTERSECTION(district_shape, block_shape)) = ST_AREA(block_shape)
GROUP BY
district_id,
office_name,
office_number
WITH
blocks AS (
SELECT
block_fips_2020,
COUNT(DISTINCT targetsmart_voterbase_id) AS total_voters,
SUM(gender_female) AS female,
SUM(gender_male) AS male,
SUM(gender_nonbinary) AS nonbinary,
ROUND(AVG(age),2) AS age,
SUM(turnout22p) AS turnout_22_p,
SUM(turnout22g) AS turnout_22_g,
SUM(turnout20p) AS turnout_20_p,
SUM(turnout20g) AS turnout_20_g,
SUM(turnout16p) AS turnout_16_p,
SUM(turnout16g) AS turnout_16_g
FROM
`deck-236019.hubble_voter.deck_person`
WHERE
reg_address_state = 'MT'
GROUP BY
block_fips_2020),
crosswalk AS (
SELECT
*
FROM
`deck-236019.hubble_geography.crosswalk_district_block_2020`),
districts AS (
SELECT
name AS office,
district_id
FROM
`deck-236019.hubble_geography.district`
WHERE
jurisdiction_state = 'MT'
AND office_name = 'State House' )
SELECT
districts.district_id AS district_id,
office,
SUM(total_voters) AS total_voters,
ROUND(SUM(female)/SUM(total_voters),3) AS pct_female,
ROUND(SUM(male)/SUM(total_voters),3) AS pct_male,
ROUND(SUM(nonbinary)/SUM(total_voters),3) AS pct_nonbinary,
ROUND(AVG(age),2) AS avg_age,
SUM(turnout_22_p) AS turnout_22_p,
SUM(turnout_22_g) AS turnout_22_g,
SUM(turnout_20_p) AS turnout_20_p,
SUM(turnout_20_g) AS turnout_20_g,
SUM(turnout_16_p) AS turnout_16_p,
SUM(turnout_16_g) AS turnout_16_g
FROM
blocks,
crosswalk,
districts
GROUP BY
district_id,
office
Last modified 1mo ago