(Skill Badge) Derive Insights from BigQuery Data: Challenge Lab

✅ 학습 과정 요약

1. Introduction to SQL for BigQuery and Cloud SQL
   BigQuery와 Cloud SQL에서 SQL 기본 문법을 배우고, 구조화된 쿼리를 실행하는 실습을 진행
2. BigQuery: Qwik Start – Console
   공개 데이터셋을 조회하고, 새로운 데이터셋을 생성하며, 데이터를 테이블에 로드하고, 사용자 정의 테이블을 쿼리하는 방법을 실습
3. BigQuery: Qwik Start – Command Line
   bq 명령줄 도구를 사용하여 공개 테이블을 조회하고 샘플 데이터를 BigQuery에 로드하는 방법을 실습
4. Explore an Ecommerce Dataset with SQL in BigQuery
   전자상거래 데이터셋에 접근하여 메타데이터를 확인하고, 중복 항목을 제거하며, 쿼리를 작성하고 실행하는 방법을 실습
5. Troubleshooting Common SQL Errors with BigQuery
   BigQuery 쿼리 편집기와 쿼리 검증기를 사용하여 SQL 문법 및 논리 오류를 식별하고 해결하는 방법을 실습
6. Explore and Create Reports with Looker Studio
   Looker Studio를 Google BigQuery 데이터 테이블에 연결하고, 차트를 생성하며, 차원과 측정값 간의 관계를 탐색하는 방법을 실습
7. Derive Insights from BigQuery Data: Challenge Lab
   이전 랩에서 배운 기술을 적용하여 BigQuery를 사용하여 실제 시나리오를 해결하는 챌린지 랩
   

✅ 이 글의 개요

1~6에서 배운 기술을 적용하여 [ 7. Derive Insights from BigQuery Data: Challenge Lab ] 의 챌린지 랩의 해결과정을 정리한 글입니다.

 

✅  이 글의 목차

Task 1. Total confirmed cases

Task 2. Worst affected areas

Task 3. Identify hotspots

Task 4. Fatality ratio

Task 5. Identifying specific day

Task 6. Finding days with zero net new cases

Task 7. Doubling rate

Task 8. Recovery rate

Task 9. CDGR - Cumulative daily growth rate

Task 10. Create a Looker Studio report

🔆Review

---

Task 1. Total confirmed cases

"확진자 총 수는 얼마였습니까?"라는 질문에 답하는 쿼리를 작성하세요. April 10, 2020?" 쿼리는 모든 국가의 확진자 수 합계를 포함하는 단일 행을 반환해야 합니다. 열 이름은 total_cases_worldwide 여야 합니다.

 

▶️ 결과

select sum(cumulative_confirmed) as total_cases_worldwide
from `bigquery-public-data.covid19_open_data.covid19_open_data`
where date = '2020-04-10'
group by date;

Task 2. Worst affected areas

"2020년 4월 10일에 사망자가 150명 이상인 미국의 주가 몇 개인가?"

이 질문에 답하기 위한 쿼리를 작성해야 하며, 출력 필드는 count_of_states 로 표시되어야 한다.

▶️ 결과

with us_sum as (select subregion1_name
				, sum(cumulative_deceased) as sum_cum
             from `bigquery-public-data.covid19_open_data.covid19_open_data`
            where country_name = 'United States of America'
              and date = '2020-04-10'
            group by subregion1_name
           having sum_cum > 150
              and subregion1_name is not null
)
select count(*) as count_of_states
  from us_sum;

Task 3. Identify hotspots

"2020년 4월 10일에 확진자가 3,000명 이상인 미국의 모든 주를 나열하시오."

이 질문에 답하기 위한 쿼리를 작성해야 하며, 반환되는 필드는 state 와 total_confirmed_cases 이고, 확진자 수를 기준으로 내림차순 정렬해야 한다.

▶️ 결과

select subregion1_name as state
     , sum(cumulative_confirmed) as total_confirmed_cases
  from `bigquery-public-data.covid19_open_data.covid19_open_data`
 where country_code = 'US'
   and date = '2020-04-10'  
   and subregion1_name is not null
 group by subregion1_name  
having total_confirmed_cases > 3000
 order by total_confirmed_cases desc

 

Task 4. Fatality ratio

"2020년 4월 동안 이탈리아의 치명률(case-fatality ratio)은 얼마였는가?"

여기서 치명률은 (총 사망자 수 / 총 확진자 수) * 100 으로 정의된다.

따라서 2020년 4월 한 달에 대해 치명률을 계산하는 쿼리를 작성해야 하며, 출력 필드는 다음과 같아야 한다

:total_confirmed_cases, total_deaths, case_fatality_ratio

▶️ 결과

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-04-01" AND "2020-04-30";

Task 5. Identifying specific day

"이탈리아에서 총 사망자 수가 10,000명을 넘어선 날은 언제인가?"

해당 질문에 답하기 위한 쿼리를 작성해야 하며, 결과는 yyyy-mm-dd 형식의 날짜로 반환되어야 한다.

▶️ 결과

select min(date) as date
  from `bigquery-public-data.covid19_open_data.covid19_open_data`
 where country_name = 'Italy'
   and cumulative_deceased > 10000;

 

Task 6. Finding days with zero net new cases

"다음 쿼리는 2020년 2월 21일부터 2020년 3월 13일 사이 인도에서 확진자 수가 증가하지 않은(0명 증가한) 날의 수를 찾기 위해 작성된 것이다. 그러나 이 쿼리가 제대로 실행되지 않고 있다."

▶️ 결과

WITH india_cases_by_date AS (
  SELECT
    date,
    SUM(cumulative_confirmed) AS cases
  FROM
    `bigquery-public-data.covid19_open_data.covid19_open_data`
  WHERE
    country_name="India"
    AND date between '2020-02-21' and '2020-03-13'
  GROUP BY
    date
  ORDER BY
    date ASC
 )
, india_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
FROM india_cases_by_date
)
SELECT
  COUNT(date) AS days_with_no_increase
FROM
  india_previous_day_comparison
WHERE
  net_new_cases = 0;

Task 7. Doubling rate

"이전 쿼리를 템플릿으로 사용하여, 2020년 3월 22일부터 2020년 4월 20일 사이 미국에서 확진자 수가 전날 대비 15% 이상 증가한 날짜(약 7일의 두 배 증가율을 의미)를 찾는 쿼리를 작성하시오.

쿼리는 해당 날짜 목록, 그 날의 확진자 수, 전날의 확진자 수, 그리고 두 날짜 간 확진자 수의 증가율(%)을 반환해야 한다."

Use the following names for the returned fields: Date, Confirmed_Cases_On_Day, Confirmed_Cases_Previous_Day and Percentage_Increase_In_Cases.

▶️ 결과

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 > 15;

Task 8. Recovery rate

"2020년 5월 10일까지 확진자가 5만 명 이상인 국가 중 회복률을 계산하여 내림차순으로 정렬하고 상위 15개 국가의 country, recovered_cases, confirmed_cases, recovery_rate를 반환하는 쿼리를 작성하시오."

▶️ 결과

WITH cases_by_country AS (
  SELECT country_name as country
      , SUM( cumulative_confirmed ) AS confirmed_cases
      , sum( cumulative_recovered ) as recovered_cases
  FROM
    `bigquery-public-data.covid19_open_data.covid19_open_data`
  WHERE date = '2020-05-10'
  group by country_name
 )
SELECT country
     , recovered_cases
     , confirmed_cases
     , ( recovered_cases / confirmed_cases ) * 100 as recovery_rate
FROM cases_by_country
WHERE confirmed_cases > 50000
order by recovery_rate desc
limit 15;

Task 9. CDGR - Cumulative daily growth rate

"다음 쿼리는 프랑스에서 첫 확진자가 보고된 2020년 1월 24일부터 2020년 4월 10일까지의 CDGR(누적 일일 성장률)을 계산하려는 것이다.

CDGR은 ((last_day_cases/first_day_cases)^(1/days_diff))-1 로 계산되며,

• last_day_cases는 2020년 5월 10일의 확진자 수
• first_day_cases는 2020년 1월 24일의 확진자 수
• days_diff는 2020년 1월 24일부터 2020년 5월 10일까지의 일수

그러나 해당 쿼리가 올바르게 실행되지 않고 있는데, 오류를 수정하여 쿼리가 정상적으로 실행되도록 할 수 있겠는가?"

▶️ 결과

WITH
  france_cases AS (
  SELECT
    date,
    SUM(cumulative_confirmed) AS total_cases
  FROM
    `bigquery-public-data.covid19_open_data.covid19_open_data`
  WHERE
    country_name="France"
    AND date IN ('2020-01-24',
      '2020-04-10')
  GROUP BY
    date
  ORDER BY
    date)
, summary as (
SELECT
  total_cases AS first_day_cases,
  LEAD(total_cases) OVER(ORDER BY date) AS last_day_cases,
  DATE_DIFF(LEAD(date) OVER(ORDER BY date),date, day) AS days_diff
FROM
  france_cases
LIMIT 1
)

select first_day_cases, last_day_cases, days_diff, POWER((last_day_cases/first_day_cases),(1/days_diff))-1 as cdgr
from summary

Task 10. Create a Looker Studio report

"미국을 대상으로 다음 데이터를 시각화하는 Looker Studio 리포트를 작성하시오:

확진자 수, 사망자 수

기간: 2020-03-26부터 2020-04-18까지"

▶️ 결과

SELECT
  date, SUM(cumulative_confirmed) AS country_cases,
  SUM(cumulative_deceased) AS country_deaths
FROM
  `bigquery-public-data.covid19_open_data.covid19_open_data`
WHERE
  date BETWEEN '2020-03-26'
  AND '2020-04-18'
  AND country_name='United States of America'
GROUP BY date

🔆Review

쿼리를 작성하는데에 있어서 주어진 요구사항이 무엇인지 정확하게 파악하는 것이 가장 중요하다. 언어가 가장 어려운 것 같다. 4번 같은 경우, 누적확진자 수이기 때문에 기간에 해당하는 값을 합계하는 게 맞는지 아직도 의문이다. 자주 사용하지 않았던 LAG(), LEAD() 문법에 대해서 공부하게 되었다.