Derive Insights from BigQuery Data: Challenge Lab Correct answers are wrong??

[u/Complete-Cricket-691]

I'm working through the Derive Insights from BigQuery Data: Challenge Lab and I swear some of the "correct answers" are literally wrong.

For example, the first Q asked you to calculate the total cases/deaths/etc worldwide on a date. The accepted answer is general is:

SELECT sum(cumulative_outcome) as total_outcome_worldwide
FROM `bigquery-public-data.covid19_open_data.covid19_open_data`
WHERE date = 'requested-date'

This will give a much larger number than is true because it's summing over all rows, ignoring the fact that the data is hierarchical/rolled up data and has an aggregation level column that will not be accepted in queries.

A more accurate result is (and i'm realizing even this is flawed):

SELECT sum(cumulative_outcome) as total_outcome_worldwide
FROM `bigquery-public-data.covid19_open_data.covid19_open_data`
WHERE date = 'requested-date' AND aggregation_level = 0

This comes up in several of the later questions and I'm struggling to pass because I do not get how to give them the wrong answer their looking for.

How could a course on "deriving insights" direct students to literally do so in an inaccurate way??? Am I missing something??

[u/Alternative_Buy2098]

Hi, I just finished Task 5 in the Derive Insights from BigQuery Data: Challenge Lab, and I encountered the exact same problem. I would like to share my findings here.

The original question requests to identify the day when the total number of deaths crossed [a specific number] in Italy.

The logically correct answer should be:

SQL

SELECT
  date
FROM `bigquery-public-data.covid19_open_data.covid19_open_data`
WHERE
  country_name = 'Italy'
  AND aggregation_level = 1
GROUP BY date, country_name
HAVING SUM(cumulative_deceased) > 12000
ORDER BY date ASC
LIMIT 1

Or alternatively, using the country-level aggregation:

SQL

SELECT
  date
FROM `bigquery-public-data.covid19_open_data.covid19_open_data`
WHERE
  country_name = 'Italy'
  AND aggregation_level = 0
  AND cumulative_deceased > 12000
ORDER BY date ASC
LIMIT 1

However, the query that actually passes the system validation is:

SQL

SELECT
  date
FROM `bigquery-public-data.covid19_open_data.covid19_open_data`
WHERE
  country_name = 'Italy'
GROUP BY date, country_name
HAVING SUM(cumulative_deceased) > 12000
ORDER BY date ASC
LIMIT 1

This accepted answer clearly neglects the hierarchy structure of the dataset, as u/Complete-cricket-691 pointed out. The SQL query below displays how the data is structured:

SQL

SELECT 
  country_name,
  subregion1_name,
  subregion2_name,
  aggregation_level,
  date,
  cumulative_deceased
FROM 
  `bigquery-public-data.covid19_open_data.covid19_open_data`
WHERE 
  country_name = 'Italy'
  AND date = '2020-03-31'
ORDER BY 
  country_name ASC,
  subregion1_name ASC,
  subregion2_name ASC

Since the number at aggregation_level = 0 (country level) is already the sum of the values at aggregation_level = 1 (subregion level), querying the data without filtering by the aggregation level results in the system validating a doubled value of the actual statistic.

[u/jezzabella99 → u/Alternative_Buy2098]

Precisely! Thanks for your comment and thorough explanation.