[SQL] 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.

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).

Approach 1. Using JOIN and DATEDIFF()

SELECT *
FROM Weather w1 JOIN Weather w2
on DATEDIFF(w1.recordDate, w2.recordDate) = 1

OUTPUT

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

정답 코드

SELECT w1.id
FROM Weather w1 JOIN Weather w2
on DATEDIFF(w1.recordDate, w2.recordDate) = 1
WHERE w1.temperature > w2.temperature

Approach 2 : Using LAG() Funcgion

Common Table Expression CTE 만들어서 수행

WITH PreviousWeatherData AS
(
    SELECT 
        id,
        recordDate,
        temperature, 
        LAG(temperature, 1) OVER (ORDER BY recordDate) AS PreviousTemperature,
        LAG(recordDate, 1) OVER (ORDER BY recordDate) AS PreviousRecordDate
    FROM 
        Weather
)

SELECT * FROM
PreviousWeatherData

Output

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





정답 코드
>
```sql
WITH PreviousWeatherData AS 
(
    SELECT id, recordDate,temperature,
    LAG(recordDate, 1) OVER (ORDER BY recordDate) AS PrevDate,
    LAG(temperature, 1) OVER (ORDER BY recordDate) AS PrevTemp
    FROM Weather
)
SELECT id
FROM PreviousWeatherData
WHERE temperature > PrevTemp
AND 
    recordDate = DATE_ADD(PrevDate, INTERVAL 1 DAY);

마지막 조건인 AND recordDate = DATE_ADD(PreviousRecordDate, INTERVAL 1 DAY);는 recordDate가 PreviousRecordDate의 다음 날인지 확인하기 위해 사용되었다.
이 조건은 온도가 이전 날짜보다 상승했을 때, 해당 날짜가 정확히 하루 후의 데이터인지 확인하려는 목적이다.

왜 이 조건이 필요한지 예시로 설명:
만약 recordDate가 반드시 연속적인 날짜가 아니라면, LAG() 함수를 사용해 이전 날짜의 데이터를 가져오더라도 그 날짜가 현재 행의 바로 전날이 아닐 수 있습니다. 예를 들어, 데이터가 주말이나 휴일 때문에 일부 날짜가 누락되었을 경우, PreviousRecordDate와 recordDate 사이에 날짜 차이가 생깁니다.

Approach 3: Using Subquery

SELECT w1.id
FROM Weather w1
WHERE w1.temperature >  
(SELECT w2.temperature
FROM Weather w2
WHERE w2.recordDate = Date_SUB(w1.recordDate, INTERVAL 1 DAY))

Using Cartesian Product and WHERE Clause

SELECT w2.id
FROM Weather w1, Weather w2
WHERE DATEDIFF(w2.recordDate, w1.recordDate) = 1 
AND w2.temperature > w1.temperature;

다른 사람 풀이

select a.id
from weather a join weather b on a.recorddate - 1 = b.recorddate
where b.temperature < a.temperature;