Question

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

SQL Schema

Create table If Not Exists Weather (Id int, RecordDate date, Temperature int)
Truncate table Weather
insert into Weather (Id, RecordDate, Temperature) values ('1', '2015-01-01', '10')
insert into Weather (Id, RecordDate, Temperature) values ('2', '2015-01-02', '25')
insert into Weather (Id, RecordDate, Temperature) values ('3', '2015-01-03', '20')
insert into Weather (Id, RecordDate, Temperature) values ('4', '2015-01-04', '30')

My Interesting Code

select temp.id as Id
from (
    select w1.id, w1.recordDate, w1.Temperature, w2.Temperature as PreTemperature
    from Weather w1
    left join Weather w2 on date_add(w2.recordDate, interval 1 day) = w1.recordDate
) temp
where temp.Temperature > temp.PreTemperature

My Perspective

For this question, firstly, you can make a new column, called “PreTemperature”, record the previous day’s temperature, using “left join”. Then, you can select the elements whose current temperature is larger than previous temperature, getting the relevant “Ids”.

More importantly, “data_add()” can help you to realize the time discrimination.

Also, there is a useful link, and I think it can help you. (chinese version)