selective_agg.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,
  
  
# here we use the CASE expression to say when the total_pop is over 500k, then include the value, otherwise count it as null
# more here: https://cloud.google.com/bigquery/docs/reference/standard-sql/conditional_expressions#case

  AVG(CASE
      WHEN total_pop > 500000 THEN total_pop
    ELSE
    NULL
  END
    )
FROM
  counties
GROUP BY
  state_fips_code