20. 미세먼지 수치의 계절간 차이

WITH base AS (
	SELECT
		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,
		measured_at,
		pm10
	FROM
		measurements m
),
ordered AS (
	SELECT
		season,
		pm10,
		ROW_NUMBER() OVER (PARTITION BY season ORDER BY pm10) row_num,
		COUNT(*) over (PARTITION BY season) total_count
	FROM
		base
)
SELECT
	season
	, ROUND(AVG(pm10), 2) AS pm10_average
	, (
		SELECT AVG(od.pm10)
		FROM ordered od
		WHERE od.season = o.season
		AND row_num IN (((total_count + 1) / 2), ((total_count + 2) / 2))
	) AS pm10_median
FROM ordered o
GROUP BY season