[LeetCode] 197. Rising Temperature

Posted on | In

Problem

Given a Weather table, write a SQL query to find all dates’ Ids with higher temperature compared to its previous (yesterday’s) dates.

1
2
3
4
5
6
7
8
+---------+------------------+------------------+
| Id(INT) | RecordDate(DATE) | Temperature(INT) |
+---------+------------------+------------------+
|       1 |       2015-01-01 |               10 |
|       2 |       2015-01-02 |               25 |
|       3 |       2015-01-03 |               20 |
|       4 |       2015-01-04 |               30 |
+---------+------------------+------------------+

For example, return the following Ids for the above Weather table:

1
2
3
4
5
6
+----+
| Id |
+----+
|  2 |
|  4 |
+----+

Explanation

  1. We can use INNER JOIN two tables. The relationship between these two table are the first table has higher temperature than the second table’s temperature, and we can use DATEDIFF to get the difference between these two table’s date, and the difference must be 1.

Solution

1
2
3
4
5
# Write your MySQL query statement below
SELECT w1.Id
FROM Weather as w1
INNER JOIN Weather as w2
ON w1.Temperature > w2.Temperature AND DATEDIFF(w1.RecordDate, w2.RecordDate) = 1;