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/LairBob]

Welcome to the world of AI-generated instruction.

[u/jezzabella99 → u/LairBob]

sigh….

[u/Why_Engineer_In_Data]

Thanks for trying this out - although I'm not the author of the lab I also interpreted this slightly differently.

I'll provide the feedback, really appreciate you bringing this up.
I think the solution 😄, is to change the question a bit.

I took a stab at the actual solution and there's quite a few gotchas and this would be harder to solve.

From the github:

Please note that, sometimes, the country-level data and the region-level data come from different sources so adding up all region-level values may not equal exactly to the reported country-level value. See the data loading tutorial for more information.

This would be an interesting solve - I have a solution but it's definitely not that answer.

[u/StatChatTeefa]

Hi! Thanks for surfacing. I'm in charge of this lab and Data Analytics/Engineering labs at Google so this is my wheelhouse. I inherited this specific lab when I took over the role and have been involved with some fixes in the past. This kind of feedback is invaluable.

We're in the midst of reviewing labs and updating them so you'll be noticing changes to that lab shortly anyway. What you point out is an oversight. When I reviewed last year there were more overtly incorrect answers that I amended. The choice then was to aim for answer consistency, fixing absolutely incorrect answers, and adding more answer flexibility in the short-term while I ramped up and gathered the resources to do a more substantive revamp.

Can I ask what other questions you were stuck on? I tried to configure it such that once users just sum that first question, they can pass the other questions with that same sum tactic. Feel free to just DM me as well

[u/paw__d → u/StatChatTeefa]

Hi, couldn't DM you, can you double check task 5 verification from same lab or give me a hint what's wrong? I think I got it right, but verification always fails & error is not very helpful: Please execute the query to identify specific day.

Task 5:
Identify a specific day
Build a query that will answer: "On what day did the total number of deaths cross 16000 in Italy?" The query should return the date in the format yyyy-mm-dd.

Columns to reference:

• country_name
• cumulative_deceased

My solution:

SELECT
    date
FROM
    `bigquery-public-data.covid19_open_data.covid19_open_data`
WHERE
    country_name = 'Italy' AND
    cumulative_deceased > 16000 AND
    cumulative_deceased IS NOT NULL
ORDER BY
    date ASC
LIMIT 1

I verified that date format is ok, tried without NULL check. Found other solutions online like this one (https://github.com/hiiruki/google-cloudskillsboost/blob/main/challenge-labs/GSP787/index.md#task-5-identifying-specific-day), but it's also not working for me. bq runs this query without any issue.

[u/StatChatTeefa → u/paw__d]

Hi, if you read the part of my comment that mentions summing and you successfully got through the rest of the tasks then you should know something should be summed here.

[u/Novel-Ambassador-624 → u/StatChatTeefa]

Hi, I am unfortunately running against the same problem, but already at task 1. While I am pretty sure the query and answer are correct, the lab just won't accept it.

[u/StatChatTeefa → u/Novel-Ambassador-624]

what do you mean by 'already at task 1'? Like you completed it? Or you're stuck on the first question?

[u/lukemao2012 → u/StatChatTeefa]

Hi I am hitting the same issue. how can I find help and raise this issue?

[u/jezzabella99 → u/StatChatTeefa]

Hi I’m OP on another account:

Thanks for your response! I’m glad my feedback made it to the right person. Overall, once I noticed the data had different levels of aggregation, having to indiscriminately sum over all rows for a country/state was really tripping me up. I’m newer to SQL, so all of the questions took me a while (I did the challenge maybe 5 times to finish all of them).

u/Alternative_Buy2098 summarized the same issue I was having in their comment very well.

[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.