저작권 문제로 인해, 직접 작성한 쿼리문만 공개.
20. 미세먼지 수치의 계절간 차이
https://solvesql.com/problems/finedust-seasonal-summary/
WITH preprocessed_table AS (
SELECT
measured_at,
season,
pm10,
ROW_NUMBER() OVER(PARTITION BY season ORDER BY pm10) AS row_num,
COUNT(*) OVER(PARTITION BY season) AS cnt
FROM (
SELECT
measured_at,
CASE
WHEN measured_at BETWEEN '2022-03-01' AND '2022-05-31' THEN 'spring'
WHEN measured_at BETWEEN '2022-06-01' AND '2022-08-31' THEN 'summer'
WHEN measured_at BETWEEN '2022-09-01' AND '2022-11-30' THEN 'autumn'
ELSE 'winter'
END AS season,
pm10
FROM measurements
) sub
)
, average_table AS(
SELECT
season,
ROUND(AVG(pm10),2) AS pm10_average
FROM preprocessed_table
GROUP BY season
)
, median_table AS (
SELECT
season,
AVG(pm10) AS pm10_median
FROM preprocessed_table
WHERE row_num IN (FLOOR((cnt+1) /2), FLOOR((cnt+2) /2))
GROUP BY season
)
-- 테이블 통합 = avg + median
SELECT
average_table.season AS season,
pm10_median,
pm10_average
FROM average_table
INNER JOIN median_table ON average_table.season = median_table.season
ORDER BY FIELD(season, 'spring', 'summer', 'autumn', 'winter')
'sql > Advent of SQL 2024' 카테고리의 다른 글
SQL 문제 풀이 챌린지(22/25) - 친구 수 집계하기 (0) | 2024.12.22 |
---|---|
SQL 문제 풀이 챌린지(21/25) - 세션 유지 시간을 10분으로 재정의하기 (0) | 2024.12.21 |
SQL 문제 풀이 챌린지(19/25) - 전국 카페 주소 데이터 정제하기 (0) | 2024.12.19 |
SQL 문제 풀이 챌린지(18/25) - 펭귄 날개와 몸무게의 상관 계수 (0) | 2024.12.18 |
SQL 문제 풀이 챌린지(17/25) - 멀티 플랫폼 게임 찾기 (0) | 2024.12.18 |