# Build and Optimize Data Warehouses with BigQuery: Challenge Lab

{% embed url="<https://www.cloudskillsboost.google/focuses/14341?parent=catalog&qlcampaign=1q-l2e-prereq>" %}

### Task 1: Create a table partitioned by date <a href="#step4" id="step4"></a>

```sql
CREATE TABLE <DATASET_NAME>.<TABLE_NAME>
PARTITION BY date
OPTIONS(partition_expiration_days=360)
AS
SELECT * 
FROM `bigquery-public-data.covid19_govt_response.oxford_policy_tracker`
WHERE alpha_3_code NOT IN ('GBR','BRA','CAN','USA');
```

### Task 2: Add new columns to your table <a href="#step5" id="step5"></a>

```sql
ALTER TABLE <DATASET_NAME>.<TABLE_NAME>
ADD COLUMN population INT64,
ADD COLUMN 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
>
```

### Task 3: Add country population data to the population column <a href="#step6" id="step6"></a>

{% tabs %}
{% tab title="Query 1" %}

```sql
CREATE OR REPLACE TABLE <DATASET_NAME>.pop_data_2019 AS
SELECT
  country_territory_code,
  pop_data_2019
FROM 
  `bigquery-public-data.covid19_ecdc.covid_19_geographic_distribution_worldwide`
GROUP BY
  country_territory_code,
  pop_data_2019
ORDER BY
  country_territory_code
```

{% endtab %}

{% tab title="Query 2" %}

```sql
UPDATE
   `<DATASET_NAME>.<TABLE_NAME>` t0
SET
   population = t1.pop_data_2019
FROM
   `<DATASET_NAME>.pop_data_2019` t1
WHERE
   CONCAT(t0.alpha_3_code) = CONCAT(t1.country_territory_code);
```

{% endtab %}
{% endtabs %}

### Task 4: Add country area data to the country\_area column <a href="#step7" id="step7"></a>

```sql
UPDATE
   `<DATASET_NAME>.<TABLE_NAME>` t0
SET
   t0.country_area = t1.country_area
FROM
   `bigquery-public-data.census_bureau_international.country_names_area` t1
WHERE
   t0.country_name = t1.country_name
```

### Task 5: Populate the mobility record data <a href="#step8" id="step8"></a>

```sql
UPDATE
   `<DATASET_NAME>.<TABLE_NAME>` t0
SET
   t0.mobility.avg_retail      = t1.avg_retail,
   t0.mobility.avg_grocery     = t1.avg_grocery,
   t0.mobility.avg_parks       = t1.avg_parks,
   t0.mobility.avg_transit     = t1.avg_transit,
   t0.mobility.avg_workplace   = t1.avg_workplace,
   t0.mobility.avg_residential = t1.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 t1
WHERE CONCAT(t0.country_name, t0.date) = CONCAT(t1.country_region, t1.date)
```

### Task 6: Query missing data in population & country\_area columns <a href="#step9" id="step9"></a>

```sql
SELECT DISTINCT country_name
FROM `<DATASET_NAME>.<TABLE_NAME>`
WHERE population IS NULL
UNION ALL
SELECT DISTINCT country_name
FROM `<DATASET_NAME>.<TABLE_NAME>`
WHERE country_area IS NULL
ORDER BY country_name ASC
```


---

# Agent Instructions: Querying This Documentation

If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.

Perform an HTTP GET request on the current page URL with the `ask` query parameter:

```
GET https://dailyjournal.gitbook.io/cloud-computing/qwiklabs-challenge-labs/build-and-optimize-data-warehouses-with-bigquery-challenge-lab.md?ask=<question>
```

The question should be specific, self-contained, and written in natural language.
The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.

Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.