emireadcode

# first, we will create a CTE to filter our NYC Taxi Data

WITH
  nyc AS (
  SELECT
    *,
    
# creating a geometry from our latitude and longitude columns
    
    st_geogpoint(pickup_longitude,

emireadcode

SELECT
  COUNT(b.full_name) AS interstates,
  a.state_name,
  ARRAY_AGG(b.full_name) AS roads
FROM
  `bigquery-public-data.geo_us_boundaries.states` a
JOIN
  `bigquery-public-data.geo_us_roads.us_national_roads` b
ON
  st_contains(a.state_geom,

emireadcode

WITH
  b AS (
  SELECT
    st_union_agg(road_geom) AS road_geom,
    full_name
  FROM
    `bigquery-public-data.geo_us_roads.us_national_roads`
  GROUP BY
    full_name )
    

emireadcode

SELECT
  state_name,
  CASE
    WHEN state_name IN ('Minnesota', 'Wisconsin', 'Iowa', 'Illinois', 'Indiana', 'Ohio', 'Michigan', 'Missouri', 'Kansas', 'Nebraska', 'North Dakota', 'South Dakota') THEN 'Midwest'
  ELSE
  'Not Midwest'
END
  AS midwest
FROM
  `bigquery-public-data.geo_us_boundaries.states`

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

# 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%"),

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 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

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

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