코딩테스트 SQL 코드카나 (Rising Temperature)

문제

Rising Temperature - LeetCode

 

 

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.

 

Example 1:

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  |
+----+
Explanation: 
In 2015-01-02, the temperature was higher than the previous day (10 -> 25).
In 2015-01-04, the temperature was higher than the previous day (20 -> 30).

 

 

---

풀이

1. recordDate에서 전날짜와 비교하여 temperature이 높은경우 id를 프린트

 

---

코드

우선 자가 조인을 한다. w2.recordDate에 1을 더한날이 w1.recordDate로 설정하여서

w1을 오늘, w2를 어제로 한다.

 

DATE_ADD를 사용하여 interval로 하루씩 올린다.

 

 

즉 오늘의 온도가 어제보다 높으면 표시

 

select w1.id
from Weather w1
join Weather w2
on w1.recordDate = DATE_ADD(w2.recordDate, interval 1 day)
where w1.temperature > w2.temperature

 

 

---

링크

LeetCode/0197-rising-temperature at main · K-MarkLee/LeetCode

LeetCode/0197-rising-temperature at main · K-MarkLee/LeetCode