728x90

BigQuery COVID-19 Lab 정리 (Task 1 ~ Task 10)

개요

이 문서는 BigQuery 공개 데이터셋인 아래 테이블을 기준으로, Task 1부터 Task 10까지의 정답 쿼리와 함께 무엇을 구하는 문제인지, 왜 이렇게 작성하는지, 핵심 SQL 포인트가 무엇인지를 흐름적으로 정리한 문서입니다.

사용 테이블:

`bigquery-public-data.covid19_open_data.covid19_open_data`

이 랩의 핵심은 단순히 SQL 문법을 쓰는 것이 아니라,

누적값(cumulative)을 어떻게 해석하는지
국가/주 단위 집계를 언제 먼저 해야 하는지
날짜 흐름 비교를 위해 LAG(), LEAD()를 어떻게 써야 하는지
비율, 증가율, 성장률을 어떤 공식으로 계산하는지

를 익히는 데 있습니다.

Task 1. Total confirmed cases

문제

2020-06-20 기준 전 세계 누적 확진자 수 합계 구하기

반환 컬럼명: total_cases_worldwide

정답 쿼리

SELECT
  SUM(cumulative_confirmed) AS total_cases_worldwide
FROM `bigquery-public-data.covid19_open_data.covid19_open_data`
WHERE date = '2020-06-20'

설명

이 문제는 특정 날짜인 2020-06-20 하루를 기준으로, 전 세계 전체 누적 확진자 수를 합산하는 문제입니다.

여기서 cumulative_confirmed 는 누적 확진자 수이므로, 해당 날짜의 값을 모두 더하면 그 날짜 기준 전체 확진자 수가 됩니다.

핵심 포인트

날짜를 먼저 WHERE로 고정합니다.
그 날짜의 cumulative_confirmed 값을 SUM() 하여 전 세계 총합을 구합니다.
국가별 세부 구분 없이 전체 합계만 필요하므로 GROUP BY가 필요하지 않습니다.

Task 2. Worst affected areas

문제

2020-06-20 기준 미국에서 누적 사망자 수가 200 초과인 주 개수 구하기

반환 컬럼명: count_of_states

정답 쿼리

WITH deaths_by_states AS (
  SELECT
    subregion1_name AS state,
    SUM(cumulative_deceased) AS death_count
  FROM `bigquery-public-data.covid19_open_data.covid19_open_data`
  WHERE country_name = 'United States of America'
    AND date = '2020-06-20'
    AND subregion1_name IS NOT NULL
  GROUP BY subregion1_name
)
SELECT
  COUNT(*) AS count_of_states
FROM deaths_by_states
WHERE death_count > 200

설명

이 문제는 미국 전체가 아니라 미국의 각 주(state) 단위로 먼저 사망자 수를 집계한 다음, 그중에서 200명을 초과한 주가 몇 개인지 세는 문제입니다.

즉, 바로 COUNT(*)를 하면 안 되고, 먼저 주별 누적 사망자 수를 계산해야 합니다.

핵심 포인트

country_name = 'United States of America' 로 미국 데이터만 제한합니다.
subregion1_name 이 주 이름 역할을 하므로 주별로 GROUP BY 합니다.
그 결과를 CTE(deaths_by_states)로 만든 뒤, 사망자 수가 200 초과인 주만 다시 세어줍니다.

왜 이렇게 해야 하는가

원본 테이블 행을 바로 세면 주 개수가 아니라 행 개수가 계산될 수 있습니다.
따라서 반드시 먼저 주별 집계를 만든 후 그 집계 결과를 기준으로 조건을 걸어야 합니다.

Task 3. Identify hotspots

문제

2020-06-20 기준 미국에서 누적 확진자 수가 2000 초과인 주 목록 구하기

반환 컬럼명:

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_name = 'United States of America'
  AND date = '2020-06-20'
  AND subregion1_name IS NOT NULL
GROUP BY subregion1_name
HAVING total_confirmed_cases > 2000
ORDER BY total_confirmed_cases DESC

설명

이번에는 주별 누적 확진자 수를 구한 뒤, 그 값이 2000 초과인 주만 뽑아내는 문제입니다.

Task 2와 구조는 거의 같지만, 이번에는 단순 개수를 세는 것이 아니라 조건을 만족하는 주 목록 자체를 보여줘야 합니다.

핵심 포인트

GROUP BY subregion1_name 으로 주별 집계를 만듭니다.
집계 결과에 대한 조건이므로 WHERE가 아니라 HAVING 을 사용합니다.
결과는 확진자 수가 많은 순으로 ORDER BY ... DESC 정렬합니다.

왜 `HAVING` 인가

SUM(cumulative_confirmed) 는 집계 결과입니다.
즉, 행 단위 조건이 아니라 그룹 단위 조건이므로 WHERE 에 쓸 수 없고 HAVING 을 써야 합니다.

Task 4. Fatality ratio

문제

2020년 5월 Italy의 치명률 구하기

공식:

(total deaths / total confirmed cases) * 100

반환 컬럼명:

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-05-01' AND '2020-05-31'

설명

이 문제는 2020년 5월 기간 동안 Italy 데이터를 기준으로 누적 확진자 수와 누적 사망자 수를 이용해 치명률을 계산하는 문제입니다.

치명률은 보통 사망자 수 ÷ 확진자 수 × 100 으로 계산합니다.

핵심 포인트

country_name = 'Italy' 로 국가를 제한합니다.
date BETWEEN '2020-05-01' AND '2020-05-31' 로 5월 범위를 지정합니다.
누적 확진자 수와 누적 사망자 수를 각각 합산한 뒤 비율을 계산합니다.

주의할 점

이 문제는 랩의 정답 기준을 따르는 것이 중요합니다. 실무적으로는 누적값을 날짜 범위 전체에 대해 합산하는 방식이 분석 목적에 따라 다르게 해석될 수 있으나, 이 랩에서는 제시된 정답 형태대로 작성하는 것이 맞습니다.

Task 5. Identify a specific day

문제

Italy에서 누적 사망자 수가 8000을 처음 넘은 날짜 구하기

정답 쿼리

SELECT
  date
FROM (
  SELECT
    date,
    SUM(cumulative_deceased) AS total_deaths
  FROM `bigquery-public-data.covid19_open_data.covid19_open_data`
  WHERE country_name = 'Italy'
  GROUP BY date
)
WHERE total_deaths > 8000
ORDER BY date ASC
LIMIT 1

설명

이 문제는 Italy의 날짜별 누적 사망자 수를 구한 뒤, 그 값이 8000을 초과하는 첫 날짜를 찾는 문제입니다.

핵심 포인트

먼저 날짜별로 SUM(cumulative_deceased) 를 계산합니다.
그 결과 중 total_deaths > 8000 인 날짜만 남깁니다.
날짜 오름차순으로 정렬 후 LIMIT 1 을 사용하면 처음 넘은 날짜를 구할 수 있습니다.

왜 서브쿼리를 쓰는가

total_deaths 는 집계 결과이므로, 이를 기준으로 필터링하려면 집계 이후 단계가 필요합니다.
그래서 먼저 날짜별 합계를 만든 뒤 바깥 쿼리에서 조건을 걸어줍니다.

Task 6. Find days with zero net new cases

문제

2020-02-25 ~ 2020-03-12 사이 India에서 전날 대비 신규 확진 증가가 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-25' AND '2020-03-12'
  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(*) AS zero_new_case_days
FROM india_previous_day_comparison
WHERE net_new_cases = 0

설명

이 문제는 누적 확진자 수 자체가 아니라, 전날 대비 얼마나 늘었는지를 계산하는 문제입니다.

누적값에서 전날 값을 빼면 해당 날짜의 순증가분(net new cases)을 구할 수 있습니다.

핵심 포인트

먼저 날짜별 누적 확진자 수를 구합니다.
LAG(cases) OVER (ORDER BY date) 를 사용해 전날 누적 확진자 수를 가져옵니다.
cases - previous_day 로 신규 증가 수를 계산합니다.
그 값이 0인 날짜의 개수를 셉니다.

왜 `LAG()` 를 쓰는가

LAG() 는 현재 행보다 이전 행의 값을 가져오는 윈도우 함수입니다.
날짜 순서 데이터에서 “전날 값과 비교”하는 문제에 가장 많이 쓰입니다.

Task 7. Doubling rate

문제

2020-03-22 ~ 2020-04-20 사이 미국에서 전날 대비 확진자 증가율이 5% 초과인 날짜 구하기

반환 컬럼명:

Date
Confirmed_Cases_On_Day
Confirmed_Cases_Previous_Day
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
  FROM us_cases_by_date
)
SELECT
  date AS Date,
  cases AS Confirmed_Cases_On_Day,
  previous_day AS Confirmed_Cases_Previous_Day,
  ((cases - previous_day) / previous_day) * 100 AS Percentage_Increase_In_Cases
FROM us_previous_day_comparison
WHERE previous_day IS NOT NULL
  AND ((cases - previous_day) / previous_day) * 100 > 5
ORDER BY date

설명

이 문제는 단순 증가량이 아니라 전날 대비 몇 퍼센트 증가했는가를 구하는 문제입니다.

증가율 공식은 아래와 같습니다.

((오늘 확진자 수 - 전날 확진자 수) / 전날 확진자 수) * 100

핵심 포인트

미국 데이터만 날짜별로 집계합니다.
LAG() 로 전날 누적 확진자 수를 가져옵니다.
증가율을 계산합니다.
증가율이 5% 초과인 날짜만 필터링합니다.

실무적으로 기억할 점

전날 값이 기준이므로, 첫 번째 날짜는 비교 대상이 없어 NULL 이 됩니다.
그래서 previous_day IS NOT NULL 조건이 필요합니다.

Task 8. Recovery rate

문제

2020-05-10 기준 회복률이 높은 국가 상위 5개 구하기

조건: 확진자 수 5만 초과

반환 컬럼명:

country
recovered_cases
confirmed_cases
recovery_rate

정답 쿼리

SELECT
  country_name AS country,
  SUM(cumulative_recovered) AS recovered_cases,
  SUM(cumulative_confirmed) AS confirmed_cases,
  SUM(cumulative_recovered) / SUM(cumulative_confirmed) AS recovery_rate
FROM `bigquery-public-data.covid19_open_data.covid19_open_data`
WHERE date = '2020-05-10'
  AND country_name IS NOT NULL
GROUP BY country_name
HAVING confirmed_cases > 50000
ORDER BY recovery_rate DESC
LIMIT 5

설명

이 문제는 국가별 회복률을 비교하는 문제입니다.
회복률은 보통 아래처럼 계산합니다.

누적 회복자 수 / 누적 확진자 수

다만 확진자 수가 너무 적은 국가는 비율 왜곡이 클 수 있으므로, 확진자 수가 5만을 초과하는 국가만 대상으로 제한합니다.

핵심 포인트

특정 날짜(2020-05-10) 기준 국가별 집계를 합니다.
HAVING confirmed_cases > 50000 으로 집계 결과에 조건을 겁니다.
회복률 내림차순 정렬 후 상위 5개만 가져옵니다.

왜 `HAVING` 인가

confirmed_cases 는 SUM() 으로 계산된 집계값이기 때문입니다.
집계 후 조건은 반드시 HAVING 을 사용해야 합니다.

Task 9. CDGR - Cumulative Daily Growth Rate

문제

France의 CDGR 계산 쿼리 오류 수정

정답 쿼리

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-06-20')
  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,
  POW((last_day_cases / first_day_cases), (1 / days_diff)) - 1 AS cdgr
FROM summary

설명

CDGR(Cumulative Daily Growth Rate)는 일정 기간 동안 누적 확진자 수가 하루 평균 어느 정도 비율로 성장했는지를 계산하는 문제입니다.

핵심은 시작일과 종료일의 누적 확진자 수를 비교하여, 전체 증가를 일 단위 성장률로 환산하는 것입니다.

핵심 포인트

France의 두 날짜(2020-01-24, 2020-06-20)만 추출합니다.
LEAD() 로 다음 행, 즉 종료일 데이터를 가져옵니다.
DATE_DIFF() 로 두 날짜 사이 일 수를 계산합니다.
성장률 공식은 POW() 를 사용해 거듭제곱 형태로 계산합니다.

왜 `LEAD()` 인가

LEAD() 는 현재 행의 다음 행 값을 가져옵니다.
시작일 행에서 종료일 확진자 수와 종료일 날짜를 같이 참조해야 하므로 적합합니다.

왜 `POW()` 인가

CDGR은 단순 차이값이 아니라 복리 개념의 평균 성장률입니다.
따라서 제곱근, 루트 같은 고정 함수가 아니라 기간 길이에 맞게 일반화할 수 있는 POW() 함수를 사용해야 합니다.

Task 10. Create a Looker Studio report

문제

Looker Studio에서 미국의 확진자 수와 사망자 수를 날짜별로 시각화하는 보고서 만들기

정답 Custom Query

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

Looker Studio 진행 순서

Blank Report 생성
BigQuery connector 선택
Authorize 클릭
프로젝트 선택
Custom Query 선택
위 SQL 입력
Add → Add to report
차트 추가
Dimension: date
Metric:
- number_of_confirmed_cases
- number_of_deaths

설명

이 문제는 SQL 자체보다도, BigQuery에서 정리한 결과를 Looker Studio 시각화용 데이터 소스로 연결하는 과정이 핵심입니다.

즉, BigQuery에서 날짜별 확진자 수와 사망자 수를 집계한 뒤, 이를 Looker Studio에서 시계열 차트로 표현하는 흐름입니다.

핵심 포인트

미국 데이터만 날짜 범위로 제한합니다.
날짜별로 확진자와 사망자 합계를 구합니다.
Looker Studio에서는 일반 테이블 선택이 아니라 Custom Query 로 직접 쿼리를 넣는 방식이 정답입니다.

한 번에 보는 핵심 문법 포인트

이번 랩에서 반복적으로 중요했던 SQL 포인트는 아래와 같습니다.

1. 원본 행을 바로 세지 말고 먼저 집계해야 하는 경우가 많음

국가별, 주별 문제는 보통 먼저 GROUP BY 로 집계한 뒤 판단해야 합니다.
그렇지 않으면 의미 없는 행 개수를 세게 됩니다.

2. 집계 후 조건은 `WHERE` 가 아니라 `HAVING`

예를 들어 아래처럼 SUM() 한 결과에 조건을 걸 때는 HAVING 을 써야 합니다.

HAVING SUM(cumulative_confirmed) > 2000

3. 전날 비교는 `LAG()`

날짜 흐름에서 이전 날짜 값을 가져와야 할 때 사용합니다.

LAG(cases) OVER (ORDER BY date)

4. 다음 행 참조는 `LEAD()`

현재 행 기준으로 다음 날짜나 다음 값을 참조할 때 사용합니다.

LEAD(total_cases) OVER (ORDER BY date)

5. 성장률 계산은 상황에 따라 다름

단순 증가율: 전날 대비 퍼센트 증가
CDGR: 기간 전체를 일평균 성장률로 환산하는 복리 개념

6. Looker Studio는 Custom Query 사용

랩에서는 단순 테이블 연결보다, 원하는 결과를 먼저 SQL로 가공한 뒤 그 결과를 보고서에 붙이는 방식이 핵심입니다.

전체 흐름 요약

이 랩은 아래 흐름으로 이해하면 정리가 쉽습니다.

특정 날짜의 전체 규모 구하기
- Task 1
국가/주 단위로 집계해서 조건에 맞는 지역 찾기
- Task 2, Task 3
비율 계산하기
- 치명률(Task 4)
- 회복률(Task 8)
특정 기준을 처음 넘는 시점 찾기
- Task 5
전날과 비교하는 시계열 분석
- 신규 증가 0인 날(Task 6)
- 전날 대비 증가율(Task 7)
기간 전체 성장률 계산하기
- CDGR(Task 9)
결과를 시각화 도구에 연결하기
- Looker Studio(Task 10)

즉, 단순 조회 → 집계 → 날짜 비교 → 비율 계산 → 성장률 계산 → 시각화로 이어지는 흐름의 랩이라고 보면 됩니다.

마무리

이번 Task 1 ~ Task 10은 단순 SQL 문법 암기가 아니라,
집계 데이터 분석을 어떻게 단계적으로 풀어가는지를 연습하는 실습입니다.

특히 아래 네 가지는 꼭 기억해두면 좋습니다.

누적값은 날짜 기준 해석이 중요하다.
집계 후 필터는 HAVING 이다.
날짜 전후 비교는 LAG(), LEAD() 를 쓴다.
증가율과 성장률은 비슷해 보여도 공식이 다르다.

728x90

저작자표시 (새창열림)

'구글 클라우드 스터디잼' 카테고리의 다른 글

Google Cloud SCC 챌린지 랩 정리 (0)	2026.04.16

BigQuery COVID-19 Lab 정리 (Task 1 ~ Task 10)

개요

Task 1. Total confirmed cases

문제

정답 쿼리

설명

핵심 포인트

Task 2. Worst affected areas

문제

정답 쿼리

설명

핵심 포인트

왜 이렇게 해야 하는가

Task 3. Identify hotspots

문제

정답 쿼리

설명

핵심 포인트

왜 HAVING 인가

Task 4. Fatality ratio

문제

정답 쿼리

설명

핵심 포인트

주의할 점

Task 5. Identify a specific day

문제

정답 쿼리

설명

핵심 포인트

왜 서브쿼리를 쓰는가

Task 6. Find days with zero net new cases

문제

정답 쿼리

설명

핵심 포인트

왜 LAG() 를 쓰는가

Task 7. Doubling rate

문제

정답 쿼리

설명

핵심 포인트

실무적으로 기억할 점

Task 8. Recovery rate

문제

정답 쿼리

설명

핵심 포인트

왜 HAVING 인가

Task 9. CDGR - Cumulative Daily Growth Rate

문제

정답 쿼리

설명

핵심 포인트

왜 LEAD() 인가

왜 POW() 인가

Task 10. Create a Looker Studio report

문제

정답 Custom Query

Looker Studio 진행 순서

설명

핵심 포인트

한 번에 보는 핵심 문법 포인트

1. 원본 행을 바로 세지 말고 먼저 집계해야 하는 경우가 많음

2. 집계 후 조건은 WHERE 가 아니라 HAVING

3. 전날 비교는 LAG()

4. 다음 행 참조는 LEAD()

5. 성장률 계산은 상황에 따라 다름

6. Looker Studio는 Custom Query 사용

전체 흐름 요약

마무리

'구글 클라우드 스터디잼' 카테고리의 다른 글

티스토리툴바

왜 `HAVING` 인가

왜 `LAG()` 를 쓰는가

왜 `HAVING` 인가

왜 `LEAD()` 인가

왜 `POW()` 인가

2. 집계 후 조건은 `WHERE` 가 아니라 `HAVING`

3. 전날 비교는 `LAG()`

4. 다음 행 참조는 `LEAD()`