SQL Notes: Rising Temperature

197. Rising Temperature

Problem

Write an SQL query to find all dates’ Id with higher temperatures compared to its previous dates (yesterday).

Return the result table in any order.

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

Solution

Algorithm

We use DATEDIFF()to compare two dates type values. The DATEDIFF() returns the difference between two date values, based on the interval specified.

MySQL

select t.Id from Weather as t, Weather as y
where Datediff (t.RecordDate, y.RecordDate) =1 
    and t.Temperature > y.Temperature

Reference

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