[ORACLE_SQL](Leet_Code)197. Rising Temperature

https://leetcode.com/problems/rising-temperature/description/?envType=study-plan-v2&envId=top-sql-50

문제요약

이전날짜(어제)보다 높은온도를 가진 row의 id를 출력하라

첫번째시도

lag함수로 이전 row의 데이터들을 가지고와서 case when 문을 써줬는데..

select ID
from
(
  select id, recordDate, temperature, date_temp, temper_temp,
  case when (recordDate = date_temp+1) and temperature > temper_temp then 1 else 0 end as num 
  from
    (select id, recordDate, temperature,
    lag(recordDate) over(order by id) as date_temp,
    lag(temperature) over(order by id) as temper_temp from weather)
)
where num = 1

10번 테스트 케이스에서 탈락해버렸다.

생각해보니 orderby를 id로 하지않고 recordDate로 하면 날짜순으로 정렬되지않을까?

정답

select ID
from
(
  select id, recordDate, temperature, date_temp, temper_temp,
  case when (recordDate = date_temp+1) and temperature > temper_temp then 1 else 0 end as num 
  from
    (select id, recordDate, temperature,
    lag(recordDate) over(order by recordDate) as date_temp,
    lag(temperature) over(order by recordDate) as temper_temp from weather)
)
where num = 1

성공!