197. Rising Temperature

문제

Table: Weather

+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| id            | int     |
| recordDate    | date    |
| temperature   | int     |
+---------------+---------+

id is the column with unique values for this table. There are no different rows with the same recordDate. This table contains information about the temperature on a certain day.

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.

Input: Weather table:

+----+------------+-------------+
| id | recordDate | temperature |
+----+------------+-------------+
| 1  | 2015-01-01 | 10          |
| 2  | 2015-01-02 | 25          |
| 3  | 2015-01-03 | 20          |
| 4  | 2015-01-04 | 30          |
+----+------------+-------------+

Output:

+----+
| id |
+----+
| 2  |
| 4  |
+----+

해설

SELECT w1.id
FROM Weather w1
JOIN Weather w2
  ON DATE_ADD(w2.recordDate, INTERVAL 1 DAY) = w1.recordDate
WHERE w1.temperature > w2.temperature;

FROM Weather w1

• Weather 테이블에서 시작 w1은 오늘 날짜를 나타내는 별칭

LEFT JOIN Weather w2 ON DATE_ADD(w1.recordDate, INTERVAL 1 DAY) = w2.recordDate

• w1과 w2는 각각 Weather 테이블의 서로 다른 날짜를 나타냄
• DATE_ADD(w1.recordDate, INTERVAL 1 DAY)는 w1의 날짜에 하루를 더한 값으로, w2.recordDate(어제 날짜)와 비교

WHERE w1.temperature > w2.temperature

• w1.temperature > w2.temperature 조건은 오늘의 온도(w1.temperature)가 어제의 온도(w2.temperature)보다 높은 경우만 필터링