join_group.sql

SELECT
  COUNT(a.vendor_id),
  b.geo_id
FROM
  PROJECT.DATASET.nyc a
JOIN
  bigquery-public-data.geo_census_blockgroups.us_blockgroups_national b
ON
  st_intersects(a.geom,
    b.blockgroup_geom)
GROUP BY b.geo_id
ORDER BY COUNT(a.vendor_id) DESC