group_by_spatial.sql

WITH
  counties AS (
  SELECT
    a.total_pop,
    b.*
  FROM
    `bigquery-public-data.census_bureau_acs.county_2018_1yr` a
  JOIN
    `bigquery-public-data.geo_us_boundaries.counties` b
  USING
    (geo_id) )
    
SELECT
  state_fips_code,
  st_union_agg(county_geom) AS geom
FROM
  counties
  
# exclude counties with less than 500 people per square mile

WHERE
  (total_pop/(st_area(county_geom)/2.59e+6)) > 500
GROUP BY
  state_fips_code