[LeetCode] Self JOIN : 197. Rising Temperature

1. 문제

Table: Weather

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

 

id is the primary key for this table. This table contains information about the temperature in a certain day.

 

Write an SQL query to find all dates' id with higher temperature compared to its previous dates (yesterday).

 

Return the result table in any order.

 

The query result format is in the following example:

 

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

Result table:
+----+
| id |
+----+
| 2  |
| 4  |
+----+

 

In 2015-01-02, temperature was higher than the previous day (10 -> 25).

In 2015-01-04, temperature was higher than the previous day (20 -> 30).

---

2. 시행착오

 

• 시행착오1 : id와 날짜는 연관성 없음 => id로 조건하면 안됨!!

# 시행착오1: id와 날짜는 연관성 없음 => id로 조건하면 안됨!!
SELECT today.id
FROM weather AS today
    INNER JOIN weather AS yesterday ON yesterday.id + 1 = today.id
WHERE today.temperature > yesterday.temperature

 

 

• 시행착오2 : recorddate는 date타입 => date타입은 +1해도 그다음 날짜를 의미하는 것X 

# 시행착오2: recorddate는 date타입 => date타입은 +1해도 그다음 날짜를 의미하는 것X 
SELECT today.id
FROM weather AS today
    INNER JOIN weather AS yesterday ON yesterday.recorddate + 1 = today.recorddate
WHERE today.temperature > yesterday.temperature

---

3. 답

SELECT today.id
FROM weather AS today
    INNER JOIN weather AS yesterday ON  DATE_ADD(yesterday.recorddate, INTERVAL 1 DAY) = today.recorddate
WHERE today.temperature > yesterday.temperature

---

4. KEY POINT

• date타입은 +1해도 그다음 날짜를 의미하는 것X => 함수 이용
  - DATA_ADD(기준날짜, INTERVAL)
  - DATA_SUB(기준날짜, INTERVAL)

---

 

leetcode.com/problems/rising-temperature/

Rising Temperature - LeetCode