[GSP] QWIKLABS GSP787 - Build and Optimize Data Warehouses with BigQuery: Challenge Lab

project id의 프로젝트에 'covid' 데이터셋 생성

 

Query 1

create table covid.oxford
PARTITION BY date
OPTIONS(
   partition_expiration_days=90
 )
as
SELECT * FROM `bigquery-public-data.covid19_govt_response.oxford_policy_tracker`
where 1=1
  and alpha_3_code not in ('GBR', 'USA')
;

 

Query 2

ALTER TABLE covid.oxford
ADD COLUMN IF NOT EXISTS population INT64,
ADD COLUMN IF NOT EXISTS country_area FLOAT64,
ADD COLUMN mobility STRUCT<
  avg_retail      FLOAT64,
  avg_grocery     FLOAT64,
  avg_parks       FLOAT64,
  avg_transit     FLOAT64,
  avg_workplace   FLOAT64,
  avg_residential FLOAT64
  >

 

Query 3

update `flor-int-200423.covid.oxford` a
set a.population = b.pop_data_2019
-- a.date = b.date, a.alpha_3_code = b.country_territory_code
FROM `bigquery-public-data.covid19_ecdc.covid_19_geographic_distribution_worldwide` b
-- where a.population = b.pop_data_2019
where a.date = b.date and a.alpha_3_code = b.country_territory_code

 

Query 4

update `flor-int-200423.covid.oxford` a
set a.country_area = b.country_area
-- a.date = b.date, a.alpha_3_code = b.country_territory_code
FROM `bigquery-public-data.census_bureau_international.country_names_area` b
-- where a.population = b.pop_data_2019
where a.country_name = b.country_name

 

Query 5

UPDATE `covid.oxford` t0
SET t0.mobility = STRUCT<avg_retail FLOAT64, avg_grocery FLOAT64, avg_parks FLOAT64, avg_transit FLOAT64, avg_workplace FLOAT64, avg_residential FLOAT64>
(t2.avg_retail, t2.avg_grocery, t2.avg_parks, t2.avg_transit, t2.avg_workplace, t2.avg_residential)
FROM (SELECT country_region, date,
      AVG(retail_and_recreation_percent_change_from_baseline) as avg_retail,
      AVG(grocery_and_pharmacy_percent_change_from_baseline)  as avg_grocery,
      AVG(parks_percent_change_from_baseline) as avg_parks,
      AVG(transit_stations_percent_change_from_baseline) as avg_transit,
      AVG( workplaces_percent_change_from_baseline ) as avg_workplace,
      AVG( residential_percent_change_from_baseline)  as avg_residential
      FROM `bigquery-public-data.covid19_google_mobility.mobility_report`
      GROUP BY country_region, date) AS t2
WHERE t0.country_name = t2.country_region
and t0.date = t2.date;

 

Query 6

SELECT distinct country_name 
FROM covid.oxford 
where population is null 
UNION ALL 
SELECT distinct country_name 
from covid.oxford where country_area is null
order by country_name asc