311_join.sql

# 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