Derive Insights from BigQuery Data: Challenge Lab Reviews

CARLOS ALBERTO G. · Reviewed أكثر من سنة ago

Henrique S. · Reviewed أكثر من سنة ago

GOOD!

Thais O. · Reviewed أكثر من سنة ago

Leandro L. · Reviewed أكثر من سنة ago

Kassia L. · Reviewed أكثر من سنة ago

ok

Fellipe G. · Reviewed أكثر من سنة ago

over complicated

Luis R. · Reviewed أكثر من سنة ago

Harri P. · Reviewed أكثر من سنة ago

Sudarshan R. · Reviewed أكثر من سنة ago

Subrata P. · Reviewed أكثر من سنة ago

Oleksii T. · Reviewed أكثر من سنة ago

This challenge lab is poorly written, missing information and details. It is impossible to complete unless searching for the solution on Google Search. Some tasks don't have enough information to complete and the way the evaluation/validating results work is shady and misleading. Here are some examples: -- Task 4. Fatality ratio /* QUERY 1 RESULT - total_confirmed_cases 20906108 - total_deaths 2057682 - case_fatality_ratio 9.842491964549307 */ WITH AGG_LOCATION AS ( SELECT SUM(cumulative_deceased) AS location_deceased , SUM(cumulative_confirmed) AS location_confirmed FROM `bigquery-public-data.covid19_open_data.covid19_open_data` WHERE DATE_TRUNC(date, MONTH) = '2020-06-01' AND UPPER(country_name) = UPPER('Italy') ) SELECT location_confirmed AS total_confirmed_cases , location_deceased AS total_deaths , (location_deceased / location_confirmed) * 100 AS case_fatality_ratio FROM AGG_LOCATION /* QUERY 2 RESULT - total_confirmed_cases 20906108 - total_deaths 2057682 - case_fatality_ratio 9.842491964549307 */ SELECT sum(cumulative_confirmed) as total_confirmed_cases , sum(cumulative_deceased) as total_deaths , (sum(cumulative_deceased)/sum(cumulative_confirmed))*100 as case_fatality_ratio FROM `bigquery-public-data.covid19_open_data.covid19_open_data` WHERE country_name="Italy" and date BETWEEN "2020-06-01" AND "2020-06-30" -- Task 5. Identifying specific day /* QUERY 1 RESULT - DATE = 2020-03-26 */ SELECT DATE FROM `bigquery-public-data.covid19_open_data.covid19_open_data` WHERE UPPER(country_name) = UPPER('Italy') AND cumulative_deceased > 8000 ORDER BY date ASC LIMIT 1 /* QUERY 2 RESULT - date = 2020-03-26 */ SELECT date FROM `bigquery-public-data.covid19_open_data.covid19_open_data` WHERE country_name="Italy" and cumulative_deceased > 8000 ORDER BY date ASC LIMIT 1 -- Task 7. Doubling rate -- FLOATING POINT??? /* QUERY 1 RESULT Date Confirmed_Cases_On_Day Confirmed_Cases_Previous_Day Percentage_Increase_In_Cases 2020-03-23 178113 144693 23.097178163421866 2020-03-24 214851 178113 20.626231661922485 2020-03-26 312220 257259 21.364072782682044 */ WITH cases_by_date AS ( SELECT date, SUM(cumulative_confirmed) AS cases FROM `bigquery-public-data.covid19_open_data.covid19_open_data` WHERE UPPER(country_name) = UPPER('United States of America') AND date between '2020-03-22' and '2020-04-20' AND cumulative_confirmed IS NOT NULL GROUP BY date ORDER BY date ASC ) , previous_day_comparison AS( SELECT date, cases, LAG(cases) OVER(ORDER BY date) AS previous_day, (cases - LAG(cases) OVER(ORDER BY date))/(LAG(cases) OVER(ORDER BY date)) AS change_ptg_new_cases FROM cases_by_date ) SELECT Date , cases AS Confirmed_Cases_On_Day , previous_day AS Confirmed_Cases_Previous_Day , change_ptg_new_cases * 100 AS Percentage_Increase_In_Cases FROM previous_day_comparison WHERE change_ptg_new_cases > 0.2 /* QUERY 2 RESULT Date Confirmed_Cases_On_Day Confirmed_Cases_Previous_Day Percentage_Increase_In_Cases 2020-03-23 178113 144693 23.097178163421866 2020-03-26 312220 257259 21.364072782682044 2020-03-24 214851 178113 20.626231661922489 */ WITH us_cases_by_date AS ( SELECT date, SUM(cumulative_confirmed) AS cases FROM `bigquery-public-data.covid19_open_data.covid19_open_data` WHERE country_name="United States of America" AND date between '2020-03-22' and '2020-04-20' GROUP BY date ORDER BY date ASC ) , us_previous_day_comparison AS (SELECT date, cases, LAG(cases) OVER(ORDER BY date) AS previous_day, cases - LAG(cases) OVER(ORDER BY date) AS net_new_cases, (cases - LAG(cases) OVER(ORDER BY date))*100/LAG(cases) OVER(ORDER BY date) AS percentage_increase FROM us_cases_by_date ) SELECT Date, cases as Confirmed_Cases_On_Day, previous_day as Confirmed_Cases_Previous_Day, percentage_increase as Percentage_Increase_In_Cases FROM us_previous_day_comparison WHERE percentage_increase > 20 -- Task 8. Recovery rate -- Say nothing about the formula for recovery rate -- Say "up to the date May 10, 2020" but the correct answer accept DATE = '2020-05-10' but not DATE <= '2020-05-10' SELECT country_name AS country , SUM(cumulative_recovered) AS recovered_cases , SUM(cumulative_confirmed) AS confirmed_cases , (SUM(cumulative_recovered) / SUM(cumulative_confirmed)) * 100 AS recovery_rate FROM `bigquery-public-data.covid19_open_data.covid19_open_data` WHERE DATE = '2020-05-10' GROUP BY 1 HAVING confirmed_cases > 50000 ORDER BY recovery_rate DESC LIMIT 20 -- Task 10 This task is undoable. The task states that the report should show the data within a data range but turns out I have to set the date range to 2020-03-15 and 2020-04-30 (according to the solution I found on the Internet). PLEASE FIX IT FOR OTHER PEOPLE WHO WILL TAKE THIS LAB IN THE FUTURE.

Nguyên T. · Reviewed أكثر من سنة ago

Harsh b. · Reviewed أكثر من سنة ago

absolutely none of my code will run

Keiran N. · Reviewed أكثر من سنة ago

Savio B. · Reviewed أكثر من سنة ago

isnt clear the level of agregation, in US New York the Query considered as correct counts the values of new york city too

Manuel M. · Reviewed أكثر من سنة ago

Dawid G. · Reviewed أكثر من سنة ago

Ayasha S. · Reviewed أكثر من سنة ago

Rohan s. · Reviewed أكثر من سنة ago

Oleg M. · Reviewed أكثر من سنة ago

Pedro S. · Reviewed أكثر من سنة ago

David L. · Reviewed أكثر من سنة ago

rodrigo m. · Reviewed أكثر من سنة ago

Mayara M. · Reviewed أكثر من سنة ago

Rotimi A. · Reviewed أكثر من سنة ago