multi_join_two.sql

WITH
  nyc AS (
  SELECT
    *
  FROM
    PROJECT.DATASET.nyc
  LIMIT
    10000)

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