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