197. Rising Temperature


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

+---------+------------+------------------+
| Id(INT) | Date(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: 
+----+
| Id |
+----+
|  2 |
|  4 |
+----+

b'
\n
\n\n
\n

Solution

\n
\n

Approach: Using JOIN and DATEDIFF() clause [Accepted]

\n

Algorithm

\n

MySQL uses DATEDIFF to compare two date type values.

\n

So, we can get the result by joining this table weather with itself and use this DATEDIFF() function.

\n

MySQL

\n
SELECT\n    weather.id AS \'Id\'\nFROM\n    weather\n        JOIN\n    weather w ON DATEDIFF(weather.date, w.date) = 1\n        AND weather.Temperature > w.Temperature\n;\n
\n
'