Warmer Than the Day Before

A network of weather stations writes one row per station per day with that day's highest temperature. Stations sometimes go offline, so some calendar days are simply missing.

Find every reading whose high was strictly warmer than the same station's reading on the previous calendar day. If the station has no reading for the day before (a gap), that day does not qualify, even if an earlier reading was colder.

Return the columns station_code and log_date, in any order. Return no rows if no reading qualifies.

Tables

temperature_log — One row per station per day it was online; ids are not in date order.

Example

temperature_log

Expected output

Rows may be returned in any order. Your query also runs against hidden datasets with tricky edge cases.

SQL Path · Joins · problem 23 of 70 · SQLite

The idea

Join the table to itself. Call one copy today and the other prev, and match a row in prev only when it is the same station and its date is exactly date(today.log_date, '-1 day'). Then keep the pairs where today's high is strictly greater.

Why this handles gaps: if the station was offline the day before, there is no matching prev row, and the inner join drops that day. A window function such as LAG looks at the previous row, which after a gap is an older day, so it gives wrong answers.

date() works on the calendar, so 2026-03-01 correctly looks back to 2026-02-28 and 2026-01-01 to 2025-12-31. The alternative uses julianday, which turns dates into day numbers so that consecutive days differ by exactly 1.

Solution

SELECT today.station_code,
       today.log_date
FROM temperature_log AS today
JOIN temperature_log AS prev
  ON prev.station_code = today.station_code
 AND prev.log_date = date(today.log_date, '-1 day')
WHERE today.high_temp_c > prev.high_temp_c;

Another way

SELECT t.station_code,
       t.log_date
FROM temperature_log AS t
WHERE EXISTS (
  SELECT 1
  FROM temperature_log AS p
  WHERE p.station_code = t.station_code
    AND julianday(t.log_date) - julianday(p.log_date) = 1
    AND p.high_temp_c < t.high_temp_c
);

Why common answers fail

LAG compares with the previous reading, not the previous calendar day, so a reading after a gap is wrongly compared with an older day.

SELECT station_code, log_date
FROM (
  SELECT station_code, log_date, high_temp_c,
         LAG(high_temp_c) OVER (PARTITION BY station_code ORDER BY log_date) AS prev_temp
  FROM temperature_log
)
WHERE high_temp_c > prev_temp;

Forgets to match the station, so a day is compared with every station's reading from the day before (and can appear more than once).

SELECT today.station_code, today.log_date
FROM temperature_log AS today
JOIN temperature_log AS prev
  ON prev.log_date = date(today.log_date, '-1 day')
WHERE today.high_temp_c > prev.high_temp_c;

Assumes ids follow the calendar, but ids are not in date order and skip across stations.

SELECT today.station_code, today.log_date
FROM temperature_log AS today
JOIN temperature_log AS prev
  ON prev.station_code = today.station_code
 AND prev.log_id = today.log_id - 1
WHERE today.high_temp_c > prev.high_temp_c;

Uses >= so a day with the same high as yesterday is counted as warmer.

SELECT today.station_code, today.log_date
FROM temperature_log AS today
JOIN temperature_log AS prev
  ON prev.station_code = today.station_code
 AND prev.log_date = date(today.log_date, '-1 day')
WHERE today.high_temp_c >= prev.high_temp_c;

What the interviewer is checking

Tests whether you can express "the previous calendar day" as a join condition instead of trusting row order or ids, and whether you spot that gaps change the answer.

Mistakes to avoid

• Using LAG or the previous id, which compares with the last reading rather than the previous day.
• Forgetting to match the station in the join.
• Comparing dates as strings with arithmetic like log_date - 1, which does not work on text dates.

Follow-ups to expect

• Return how many degrees warmer it was (select today.high_temp_c - prev.high_temp_c).
• Find runs of three or more consecutively warmer days (gaps and islands with ROW_NUMBER).
• Compare with the same day last year instead (date(log_date, '-1 year')).

In real work

Day-over-day comparisons on sensor, sales or traffic data where missing days must not be silently bridged.

Practice next