Rising Temperature SQL Query | SQL Interview Question

Problem statement

Prompt

Given the weather table below, write a query that returns the `id` of every day whose temperature was strictly higher than the temperature of the previous calendar day recorded in the table\*\* (the day before it, if that day exists in the table).

Only the id column should be returned.
The first recorded day has no previous day and can never qualify.
A day is not "higher" if the previous day's temperature was equal, lower, or missing.
weather.temperature may be NULL; comparisons involving NULL never qualify.

Example

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

Expected result:

id
2
4

Constraints

weather.id is the primary key.
record_date has at most one row per day (single station).

Tips

LAG(temperature) OVER (ORDER BY record_date) attaches the previous day's temperature in one pass.
A self join on the previous date also works — the join key is unique, so no fan-out.

Schema

CREATE TABLE weather (
    id          INTEGER PRIMARY KEY,
    record_date DATE NOT NULL,
    temperature INTEGER  -- NULL allowed
);

Sample data

INSERT INTO weather (id, record_date, temperature) VALUES
  (1, '2024-01-01', 10),
  (2, '2024-01-02', 25),
  (3, '2024-01-03', 20),
  (4, '2024-01-04', 30),
  (5, '2024-01-05', 28),
  (6, '2024-01-06', 35);

Additional hidden fixtures are applied during validation to test edge cases.

Solution

One correct approach — try solving it yourself in the practice editor first, then compare. There is usually more than one valid solution.

-- Reference: strictly higher than the previous recorded day (via LAG).
WITH prev AS (
    SELECT id, record_date, temperature,
           LAG(temperature) OVER (ORDER BY record_date) AS prev_temp
    FROM weather
)
SELECT id
FROM prev
WHERE temperature > prev_temp
ORDER BY id;

Key concepts

LAG
Self join
Date arithmetic
Strict comparison

Rising Temperature