lfy79001

WITH bounding_area AS (SELECT geometry FROM `bigquery-public-data.geo_openstreetmap.planet_features`
        WHERE feature_type="multipolygons"
          AND ('wikidata', 'Q62') IN (SELECT (key, value) FROM unnest(all_tags))
     )
SELECT count(*) AS stops_count,
       (SELECT value FROM unnest(all_tags) WHERE key='network') AS bus_network  -- Extract value of "network" tag
  FROM `bigquery-public-data.geo_openstreetmap.planet_features` planet_features, bounding_area
 WHERE feature_type = 'points'
   AND ('highway', 'bus_stop') IN (SELECT (key, value) FROM UNNEST(all_tags)) -- Select bus stops
   AND ST_DWithin(bounding_area.geometry, planet_features.geometry, 0)  -- Filter only features within bounding_area

lfy79001

WITH bounding_area as (SELECT geometry from `bigquery-public-data.geo_openstreetmap.planet_features`
        WHERE feature_type="multipolygons"
          AND ('name:en', 'Netherlands') in (SELECT (key, value) from unnest(all_tags))
          AND ('boundary', 'administrative') in (SELECT (key, value) from unnest(all_tags))
          AND ('admin_level', '3') in (SELECT (key, value) from unnest(all_tags))
     )
SELECT feature_type, osm_id, osm_timestamp, planet_features.geometry
  FROM `bigquery-public-data.geo_openstreetmap.planet_features` planet_features, bounding_area
 WHERE ('man_made', 'bridge') IN (SELECT (key, value) FROM UNNEST(all_tags)) -- Select features with 'man_made=bridge' tag
   AND ST_DWithin(bounding_area.geometry, planet_features.geometry, 0)  -- Filter only features within bounding_area

lfy79001

SELECT
    YEAR(ts.date) AS year,
    COUNT(DISTINCT ts.date) AS count_severe_weather_days
FROM cybersyn.noaa_weather_metrics_timeseries AS ts
JOIN cybersyn.noaa_weather_station_index AS idx
    ON (ts.noaa_weather_station_id = idx.noaa_weather_station_id)
WHERE 
    ts.variable_name = 'Weather Type: Tornado, Waterspout, or Funnel Cloud'
    AND idx.state_name = 'Florida'
    AND ts.value = 1

lfy79001

SELECT geo.geo_name,
       ts.variable_name,
       ts.date,
       ts.value
FROM cybersyn.our_world_in_data_timeseries AS ts
JOIN cybersyn.geography_index AS geo
  ON (geo.geo_id = ts.geo_id)
WHERE ts.variable_name = 'Carbon intensity'
  AND geo.geo_name IN ('United States', 'United Kingdom', 'European Union')
ORDER BY ts.date;

lfy79001

SELECT geo.geo_name,
       att.variable_name,
       ts.date, ts.value
FROM cybersyn.bureau_of_labor_statistics_employment_timeseries AS ts
JOIN cybersyn.bureau_of_labor_statistics_employment_attributes AS att
    ON (ts.variable = att.variable)
JOIN cybersyn.geography_index AS geo
    ON (ts.geo_id = geo.geo_id)
WHERE att.report = 'State and Metro Employment'
  AND att.industry = 'Financial Activities'

lfy79001

WITH big_banks AS (
    SELECT id_rssd
    FROM cybersyn.financial_institution_timeseries
    WHERE variable = 'ASSET'
      AND date = '2022-12-31'
      AND value > 1E10
)
SELECT name,
       1 - value AS pct_uninsured,
       ent.is_active

lfy79001

WITH acs_2017 AS (
  SELECT geo_id, income_less_10000 AS i10, income_10000_14999 AS i15, income_15000_19999 AS i20
  FROM `bigquery-public-data.census_bureau_acs.county_2017_5yr`
 ),

snap_2017_Jan AS (
  SELECT FIPS, SNAP_All_Participation_Households AS snap_total
  FROM `bigquery-public-data.sdoh_snap_enrollment.snap_enrollment`
  WHERE Date = '2017-01-01'
),

lfy79001

WITH avg_wage_1998 AS(
  SELECT
    ROUND(AVG(avg_wkly_wage_10_total_all_industries) * 52,2) AS wages_1998
  FROM
    `bigquery-public-data.bls_qcew.1998*`
  WHERE
    geoid = "42003" --Selecting Allgeheny County
),
    
avg_wage_2017 AS (

lfy79001

WITH
  num_vaccine_sites_per_county AS (
  SELECT
    facility_sub_region_1 AS us_state,
    facility_sub_region_2 AS us_county,
    facility_sub_region_2_code AS us_county_fips,
    COUNT(DISTINCT facility_place_id) AS num_vaccine_sites
  FROM
    bigquery-public-data.covid19_vaccination_access.facility_boundary_us_all
  WHERE

lfy79001

SELECT *
FROM
 (
   SELECT
     '2022 Google Hiring' AS data_source,
     race_asian,
     race_black,
     race_hispanic_latinx,
     race_native_american,
     race_white,