다음날도 서울숲의 미세먼지 농도는 나쁨

https://solvesql.com/problems/bad-finedust-measure/

• 오답

WITH m2 AS(
  SELECT measured_at
       , DATE_ADD(measured_at, interval 1 day) next_day
       , pm10 next_pm10
  FROM measurements
)
SELECT m1.measured_at today
     , next_day
     , pm10
     , next_pm10
FROM measurements m1
  JOIN m2 ON m1.measured_at = m2.next_day
WHERE pm10 < next_pm10

• 정답

WITH m2 AS(
  SELECT measured_at today
       , pm10
       , DATE_ADD(measured_at, interval 1 day) next_day
       , LEAD(pm10) OVER (ORDER BY measured_at) next_pm10
  FROM measurements
)
SELECT today
     , next_day
     , pm10
     , next_pm10
FROM m2
WHERE pm10 < next_pm10

Lesson & Learned

• 다음날 데이터만 WITH문으로 m2 테이블 만들어서
  m1 과 조인해서 가져오려고 했는데, 아무것도 조회되지 않았다.
  이유는 next_pm10을 잘못 지정했다.

• DATE_ADD(date, interval n intervaltype)
  DATE_SUB(date, interval n intervaltype)
  LEAD( ) OVER ( ) 윈도우함수