The Rising Temperature interview question is a SQL self-join problem. Given a table of daily temperature records (with date and temperature columns), find all dates where the temperature is higher than the temperature on the previous day. Return the IDs of those records.
This database problem is asked at Apple, Cisco, Microsoft, Meta, Amazon, Google, Bloomberg, and Adobe because it tests a fundamental SQL technique: comparing a row with its preceding row. Self-joins and date arithmetic are critical skills in time-series analysis, financial reporting, IoT sensor data processing, and any domain where consecutive-day comparisons are needed.
The pattern is self-join with date arithmetic. Join the Weather table with itself, matching today's record with yesterday's: w1.recordDate = w2.recordDate + INTERVAL 1 DAY (or equivalent date function). Filter where w1.temperature > w2.temperature. Return w1.id.
SELECT w1.id
FROM Weather w1
JOIN Weather w2
ON w1.recordDate = DATE_ADD(w2.recordDate, INTERVAL 1 DAY)
WHERE w1.temperature > w2.temperature;
A window function alternative: use LAG(temperature) OVER (ORDER BY recordDate) to access the previous day's temperature in a CTE, then filter.
Weather table:
| id | recordDate | temperature |
|---|---|---|
| 1 | 2024-01-01 | 15 |
| 2 | 2024-01-02 | 18 |
| 3 | 2024-01-03 | 14 |
| 4 | 2024-01-04 | 20 |
Self-join matches:
Result: [2, 4].
DATE_ADD / DATE_SUB — date arithmetic varies by SQL dialect.DATEDIFF(w1.recordDate, w2.recordDate) = 1 which is equivalent but platform-specific.For the Rising Temperature coding problem, the database interview pattern is self-join with date arithmetic. Know both the self-join approach and the LAG() window function approach — the latter is cleaner for sequential comparisons. Interviewers at Deloitte and Cognizant may ask "what if there are missing dates?" — the join handles this naturally since there will be no matching pair. Practice both SQL dialects (MySQL's DATE_ADD vs PostgreSQL's + interval syntax).