1. Rising Temperature (LeetCode)
Model answer
The task
Find each weather record whose temperature is higher than the temperature recorded on the immediately preceding calendar day. Return the IDs of those records.
Key idea
Join the table to itself, matching each record with the record dated exactly one day earlier. Comparing only adjacent rows would be incorrect because dates may have gaps.
Approach
- Treat one copy of the table as the current day and another as the previous day.
- Join them where the current date is one day after the previous date.
- Keep pairs where the current temperature is higher.
- Return the current record’s ID.
SELECT current_day.id AS Id
FROM Weather AS current_day
JOIN Weather AS previous_day
ON current_day.recordDate = DATE_ADD(previous_day.recordDate, INTERVAL 1 DAY)
WHERE current_day.temperature > previous_day.temperature;
Complexity:
- Time: O(n log n) with an index on
recordDate; the exact cost depends on the database’s query plan. - Space: O(1) auxiliary space, excluding space used for the result and any database join workspace.
Common follow-ups
- What if there are missing dates? The exact one-day join naturally skips dates without a prior-day record.
- How could this perform better on a large table? Add an index on
recordDateand inspect the database’s execution plan. - How would you adapt this for another SQL dialect? Replace
DATE_ADDwith that dialect’s date arithmetic, such as a one-day interval expression.