Skip to content

Instantly share code, notes, and snippets.

@mbforr
Created Jul 30, 2021
Embed
What would you like to do?
# create an aggregated CTE first
WITH
zips_311 AS (
SELECT
incident_zip,
COUNT(unique_key) AS count
FROM
`bigquery-public-data.new_york_311.311_service_requests`
GROUP BY
incident_zip )
# join the CTE table below using zip code
SELECT
b.zip_code_geom,
a.incident_zip,
a.count
FROM
zips_311 a
JOIN
`bigquery-public-data.geo_us_boundaries.zip_codes` b
ON
a.incident_zip = b.zip_code
ORDER BY
a.count
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment