Rising Temperature

easy hash map database date diff

Problem

Find all dates' Ids in Weather whose temperature is higher than the previous day's temperature.

SQL: SELECT w1.id FROM Weather w1 JOIN Weather w2 ON DATEDIFF(w1.recordDate, w2.recordDate) = 1 WHERE w1.temperature > w2.temperature;

InputWeather = [(1, 2015-01-01, 10), (2, 2015-01-02, 25), (3, 2015-01-03, 20), (4, 2015-01-04, 30)]

Output[2, 4]

Weather (id|date|temp; …)

SELECT w1.id FROM Weather w1 JOIN Weather w2
  ON DATEDIFF(w1.recordDate, w2.recordDate) = 1
WHERE w1.temperature > w2.temperature;

Explanation

We want each day whose temperature is higher than the day before. The neat SQL trick is a self-join: we join the Weather table to itself so that each row can be paired with its own previous day.

We call the two copies w1 (today) and w2 (yesterday). The join condition DATEDIFF(w1.recordDate, w2.recordDate) = 1 keeps only pairs where w1's date is exactly one day after w2's date.

With each today-yesterday pair lined up, the WHERE w1.temperature > w2.temperature filter keeps only the pairs where the temperature actually rose, and we select w1.id for those days.

Example: dates 01-01 (10°), 01-02 (25°), 01-03 (20°), 01-04 (30°). 01-02 beats 01-01 and 01-04 beats 01-03, so the result is ids [2, 4]. 01-03 dropped from 25° to 20°, so it is excluded.

Time: O(n) Space: O(n)