Skip to content

Instantly share code, notes, and snippets.

@mbforr
Created July 25, 2021 02:22
  • Star 1 You must be signed in to star a gist
  • Fork 1 You must be signed in to fork a gist
Star You must be signed in to star a gist
Save mbforr/fb710d91db5e3111b29e5204949095a0 to your computer and use it in GitHub Desktop.
# 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