I am trying to understand the solution to the following leetcode problem: https://leetcode.com/problems/rising-temperature/
The solution given was as follows:
SELECT weather.id AS 'Id'
FROM weather
JOIN
weather w ON DATEDIFF(weather.date, w.date) = 1
AND weather.Temperature > w.Temperature;
The problem requires calculation of delta between the current row's temperature and previous temperature. How is this being achieved in the solution above? It looks like the comparison is being done between the same row of left and right tables.
Since the data in the table is in the form of one value per date, the previous temperature has a RecordDate value that is one day earlier, so to compare the values the table is JOINed to itself on that condition (i.e. DATEDIFF(w2.RecordDate, w1.RecordDate) = 1), and the condition that the new row's temperature is higher than the previous row (w2.Temperature > w1.Temperature) i.e.
SELECT *
FROM Weather w1
JOIN Weather w2 ON DATEDIFF(w2.RecordDate, w1.RecordDate) = 1
AND w2.Temperature > w1.Temperature
If you look at all the output values from that query you can see that for each row, the RecordDate values for w2 are one day later than w1, and the temperature for w2 is higher than that for w1:
Id RecordDate Temperature Id RecordDate Temperature
1 2015-01-01 00:00:00 10 2 2015-01-02 00:00:00 25
3 2015-01-03 00:00:00 20 4 2015-01-04 00:00:00 30
So from that result it is just a question of selecting only the w2.Id values i.e.
SELECT w2.Id AS Id
FROM Weather w1
JOIN Weather w2 ON DATEDIFF(w2.RecordDate, w1.RecordDate) = 1
AND w2.Temperature > w1.Temperature
Output
Id
2
4