Skip to content

Instantly share code, notes, and snippets.

@mbforr
Created July 29, 2021 17: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/208c40f1a6cae4175fbe7aae2ec7ae28 to your computer and use it in GitHub Desktop.
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
state_fips_code,
AVG(total_pop)
FROM
counties
GROUP BY
state_fips_code
# we can use having to show states where average county populations are over 500,000
HAVING
AVG(total_pop) > 500000
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment