having.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,
  AVG(total_pop)
FROM
  counties
GROUP BY
  state_fips_code

  # we can use having to show states where average county populations are over 500,000

HAVING
  AVG(total_pop) > 500000