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

# 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

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

emireadcode

SELECT
  *
FROM
  `bigquery-public-data.geo_us_boundaries.states`
WHERE
  state_name BETWEEN 'Alaska'
  AND 'Kentucky'
ORDER BY
  state_name ASC

emireadcode

SELECT
 DISTINCT name,
 st_y(geom)
FROM
  `YOUR_PROJECT.YOUR_DATASET.airports`
WHERE
  country = 'CA'
ORDER BY
  st_y(geom) DESC,
  name DESC

emireadcode

SELECT
 name,
 st_y(geom)
FROM
  `YOUR_PROJECT.YOUR_DATASET.airports`
WHERE
  country = 'CA'
ORDER BY
  st_y(geom) ASC,
  name ASC

emireadcode

SELECT
  DISTINCT name,
  latitude
FROM
  `cartodb-gcp-solutions-eng-team.matt.airports`
WHERE
  country = 'CA'
ORDER BY
  latitude ASC,
  name DESC

emireadcode

create table `ADD_YOUR_PROJECT.ADD_YOUR_DATASET.airports` as

select
  a.*,
  b.geom as polygon,
  c.country as country_name
from
  `bigquery-public-data.geo_whos_on_first.spr` a
join
  `bigquery-public-data.geo_whos_on_first.geojson` b