[LeetCode] 97. Rising Temperature

문제

Write a solution to find all dates' id with higher temperatures compared to its previous dates (yesterday).

Return the result table in any order.

The result format is in the following example.

 

전날보다 온도가 높은 날의 id값을 출력하면 된다.

 

쿼리 #1

SELECT a.id AS Id
FROM Weather a
JOIN Weather b
  ON DATEDIFF(a.recordDate, b.recordDate) = 1
 AND a.temperature > b.temperature;

 

쿼리 #2(LAG 활용)

SELECT id
FROM (
    SELECT 
        id,
        recordDate,
        temperature,
        LAG(recordDate) OVER (ORDER BY recordDate) AS prev_date,
        LAG(temperature) OVER (ORDER BY recordDate) AS prev_tmp
    FROM Weather
) A
WHERE DATEDIFF(A.recordDate, A.prev_date) = 1
  AND A.prev_tmp < A.temperature;

 

 

 

 

문제 출처

https://leetcode.com/problems/rising-temperature/description/