interstates_first.sql

SELECT
  COUNT(b.full_name) AS interstates,
  a.state_name,
  ARRAY_AGG(b.full_name) AS roads
FROM
  `bigquery-public-data.geo_us_boundaries.states` a
JOIN
  `bigquery-public-data.geo_us_roads.us_national_roads` b
ON
  st_contains(a.state_geom,
    b.road_geom)
WHERE
  b.full_name LIKE 'I-%'
  AND st_LENGTH(b.road_geom) > 5000
GROUP BY
  a.state_name