Skip to content

Instantly share code, notes, and snippets.

@mbforr
Created Jul 25, 2021
Embed
What would you like to do?
# using a CTE or common table expression here to join to datasets - geometries and county statistics from 2018
# we will cover this in a future post but you can now treat 'counties' as its own table in the context of this query
WITH
counties AS (
SELECT
a.total_pop,
b.*
FROM
`bigquery-public-data.census_bureau_acs.county_2018_1yr` a
JOIN
`bigquery-public-data.geo_us_boundaries.counties` b
USING
(geo_id) )
SELECT
SUM(total_pop),
AVG(total_pop),
MIN(total_pop),
MAX(total_pop)
FROM
counties
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment