agg.sql

# using a CTE or common table expression here to join to datasets - geometries and county statistics from 2018
# we will cover this in a future post but you can now treat 'counties' as its own table in the context of this query

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
  SUM(total_pop),
  AVG(total_pop),
  MIN(total_pop),
  MAX(total_pop)
FROM
  counties