Find all dates' Ids with higher temperatures compared to its previous dates (yesterday).
This challenge now meets the current minimum standard for sitemap inclusion and independent indexing.
Find all dates' Ids with higher temperatures compared to its previous dates (yesterday).
Day-over-day comparisons show up constantly in analytics work: revenue growth, retention movement, traffic spikes, and sensor changes all use the same pattern. This problem teaches the underlying idea of joining or windowing a time series against its previous observation.
idrecordDatetemperatureSELECT
w1.id
FROM weather w1
JOIN weather w2
ON DATE(w1.recordDate) = DATE(w2.recordDate, '+1 day')
WHERE w1.temperature > w2.temperatureThis approach pairs each row with the row from exactly one day earlier, then keeps only the rows where the current temperature is higher. It is easy to reason about because the previous day is modeled directly as another copy of the same table.
WITH temps AS (
SELECT
id,
recordDate,
temperature,
LAG(recordDate) OVER (ORDER BY recordDate) AS prev_date,
LAG(temperature) OVER (ORDER BY recordDate) AS prev_temp
FROM weather
)
SELECT
id
FROM temps
WHERE DATE(prev_date) = DATE(recordDate, '-1 day')
AND temperature > prev_tempThe window-function version is useful when you already need ordered row-to-row comparisons. It is more scalable conceptually because you can extend it to growth rates, rolling deltas, and longer sequences without adding more self joins.
Do not assume the previous row is always yesterday unless you check the date gap explicitly. If the table skips dates, comparing to the previous row without verifying the one-day difference will produce false positives.
IDs often increase with insertion order, not calendar order. The business condition is about yesterday, so the join must be based on date arithmetic rather than assuming consecutive IDs mean consecutive days.
Use LAG when you are doing a broader time-series analysis pipeline and already need ordered row comparisons. It keeps the logic in one ordered pass and is easier to extend to percent change or streak detection.
Use these guides when you want the fuller explanation behind the SQL pattern this challenge is testing.
Turn raw timestamps into business insights. Learn how to calculate Month-over-Month growth and smooth out noisy data with 7-day moving averages.
Stop struggling with dates in SQL. Learn how to handle current time, date arithmetic, formatting, and time differences using standard SQL and SQLite syntax.
Stop being confused by joins on the same table. Learn how to master Self Joins for organizational hierarchies, sequential data comparison, and more.
If this problem exposed a deeper weakness, use a topic hub to move through a curated cluster instead of random article hopping.
Analytics
A practical cluster for cohorts, funnels, customer value, and the kinds of reporting questions teams actually ask every week.
Advanced SQL
Use this path when you are moving beyond beginner SELECT queries into CTEs, window functions, and conditional logic.
If you want the rotating practice flow, open this same question from the daily stream. If you want interview-style packaging, continue into the interview pool after this rep.
Assign tied scores the same rank while keeping the next rank consecutive with no gaps.
Compute daily cancellation rate after excluding any trip where either the client or driver is banned.
Reconstruct each product’s price as of a snapshot date, including products that had not changed yet and still use the default price.