cross_join.sql

# create a CTE with our county data

WITH
  counties AS (
  SELECT
    a.median_income,
    b.*
  FROM
    `bigquery-public-data.census_bureau_acs.county_2018_5yr` a
  JOIN
    `bigquery-public-data.geo_us_boundaries.counties` b
  USING
    (geo_id) )

# first we set up our aggregations

SELECT
  a.county_fips_code,
  a.county_name,
  AVG(b.median_income) as neighbor_county_median_income,
  a.median_income as target_county_median_income
FROM
  counties a

# using a cross join we can join each row of the counties table to itself, using a spatial relationship
  
CROSS JOIN
  counties b

# using st_touches, we can then see which counties touch the target county in each row
  
WHERE
  st_touches(a.county_geom,
    b.county_geom)
    
# make sure to exlcude the target county from the cross join

  AND a.county_fips_code != b.county_fips_code
GROUP BY
  a.county_fips_code,
  a.median_income,
  a.county_name