emireadcode

SELECT
  *
FROM
  `bigquery-public-data.geo_us_boundaries.states`
WHERE
  state_name IN ('Minnesota',
    'Wisconsin',
    'Iowa',
    'Illinois')
ORDER BY

emireadcode

# 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

emireadcode

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

emireadcode

SELECT
  st_union_agg(county_geom) AS geom,
  state_fips_code
FROM
  `bigquery-public-data.geo_us_boundaries.counties`
GROUP BY
  state_fips_code

emireadcode

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

emireadcode

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

emireadcode

SELECT
  start_station_id,
  start_station_name,
  start_station_latitude,
  start_station_longitude,
  
  # calculate the average distance for all trips from each start station
  
  AVG(st_distance(st_geogpoint(start_station_latitude,
        start_station_longitude),

emireadcode

# create an aggregated CTE first

WITH
  zips_311 AS (
  SELECT
    incident_zip,
    COUNT(unique_key) AS count
  FROM
    `bigquery-public-data.new_york_311.311_service_requests`
  GROUP BY

emireadcode

# 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

emireadcode

# create a bounding area from the New York CBSA geometry

WITH
  bounding_area AS (
  SELECT
    geom
  FROM
    `carto-do-public-data.carto.geography_usa_cbsa_2019`
  WHERE
    do_label LIKE "%New York%"),