✏️ Explanatory Question
Level: Coding Round — A LeetCode classic; the trap is comparing by the previous CALENDAR day, not the previous row.
The Puzzle: Given a weather table with a date and temperature, return the id of all days where the temperature was higher than the day immediately before it (the previous calendar date).
weather| id | record_date | temperature |
|---|---|---|
| 1 | 2026-08-01 | 30 |
| 2 | 2026-08-02 | 35 |
| 3 | 2026-08-03 | 32 |
| 4 | 2026-08-05 | 36 |
| id |
|---|
| 2 |
Day 2 (35) > Day 1 (30) → qualifies. Day 3 (32) < Day 2 (35) → no. Day 4 is Aug 5, but Aug 4 is missing, so there is no "previous day" → excluded.
The critical trap: You must compare against the previous calendar date (date - 1 day), NOT simply the previous row by id. Notice Aug 5's row exists but Aug 4 does not — so Aug 5 must be excluded because there is no record for the day before it. Comparing by row order would wrongly compare Aug 5 to Aug 3.
SELECT w1.id
FROM weather w1
JOIN weather w2
ON w1.record_date = w2.record_date + INTERVAL 1 DAY -- w2 is the day before
WHERE w1.temperature > w2.temperature;
-- DATEDIFF variant (same idea, explicit 1-day difference)
SELECT w1.id
FROM weather w1
JOIN weather w2 ON DATEDIFF(w1.record_date, w2.record_date) = 1
WHERE w1.temperature > w2.temperature;SELECT id
FROM (
SELECT id, record_date, temperature,
LAG(temperature) OVER (ORDER BY record_date) AS prev_temp,
LAG(record_date) OVER (ORDER BY record_date) AS prev_date
FROM weather
) t
WHERE temperature > prev_temp
AND record_date = prev_date + INTERVAL 1 DAY; -- ensure it's the ACTUAL prev day Why the date guard matters with LAG: LAG grabs the previous row, which may not be the previous day if dates are missing. The extra condition record_date = prev_date + INTERVAL 1 DAY filters out cases where the previous row is not actually yesterday — correctly excluding Aug 5 in our data.
The self-join is clean and, with an index on record_date, efficient. The window-function version reads the table once but must sort by date. Both are acceptable; mention that an index on record_date is what keeps either solution fast.
Interviewer follow-up: "Return days warmer than the average of the previous 3 days instead." → Switch to a window frame: AVG(temperature) OVER (ORDER BY record_date ROWS BETWEEN 3 PRECEDING AND 1 PRECEDING), then compare the current temperature to it. But keep the date-continuity guard if gaps must be respected — the same missing-day trap applies.