Compare Sequential Daily Records with Self-Join in SQL

Question

Given a `weather` table with `id`, `record_date`, and `temperature`, write a SQL query to find all record IDs where the temperature was higher than the previous day's (yesterday's) temperature.

Accepted Answer

Optimal Solution:
SELECT w1.id
FROM weather w1
JOIN weather w2 
  ON w1.record_date = w2.record_date + INTERVAL '1 day'
WHERE w1.temperature > w2.temperature;

Technical Explanation:
A self-join connects today's record (`w1`) with yesterday's record (`w2`) using date arithmetic `w1.record_date = w2.record_date + INTERVAL '1 day'`, then filters where `w1.temperature > w2.temperature`.

id	record_date	temperature
1	2026-01-01	10
2	2026-01-02	25
3	2026-01-03	20
4	2026-01-04	30