리뷰 BigQuery 데이터에서 인사이트 도출: 챌린지 실습개

Henrique S. · 11개월 전에 리뷰됨

GOOD!

Thais O. · 11개월 전에 리뷰됨

Leandro L. · 11개월 전에 리뷰됨

Kassia L. · 11개월 전에 리뷰됨

ok

Fellipe G. · 11개월 전에 리뷰됨

over complicated

Luis R. · 11개월 전에 리뷰됨

Harri P. · 11개월 전에 리뷰됨

Sudarshan R. · 11개월 전에 리뷰됨

Subrata P. · 11개월 전에 리뷰됨

Oleksii T. · 11개월 전에 리뷰됨

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. · 11개월 전에 리뷰됨

Harsh b. · 11개월 전에 리뷰됨

absolutely none of my code will run

Keiran N. · 11개월 전에 리뷰됨

Savio B. · 11개월 전에 리뷰됨

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. · 11개월 전에 리뷰됨

Dawid G. · 11개월 전에 리뷰됨

Ayasha S. · 11개월 전에 리뷰됨

Rohan s. · 11개월 전에 리뷰됨

Oleg M. · 11개월 전에 리뷰됨

Pedro S. · 11개월 전에 리뷰됨

David L. · 11개월 전에 리뷰됨

rodrigo m. · 11개월 전에 리뷰됨

Mayara M. · 11개월 전에 리뷰됨

Rotimi A. · 11개월 전에 리뷰됨

Krishna S. · 11개월 전에 리뷰됨