Interview Question Bank · Self-join on date - 1 · missing days
3. Warmer than the day before
EasyGeneralQuestion #195
Concept: Self-join on date - 1 · missing daysshow
To compare a day with the previous calendar day, join the table to itself on b.day = a.day - 1 (in Postgres, a date minus an integer is that many days earlier). LAG gives the previous row instead, which is only the previous day when no day is missing. With gaps in the data, the date join is the safe choice: a day whose neighbour is missing simply finds no match.
Example (different data):
SELECT today.sales_day, today.orders, yday.orders AS orders_day_before FROM daily_orders today JOIN daily_orders yday ON yday.sales_day = today.sales_day - 1;
Your task
The Saltmarsh field station wants every day in March 2025 whose high was warmer than the day right before it. The logger was down on a few days, and a day with no reading for the previous day can't be compared.
Return reading_date, high_c and prev_day_high (the previous calendar day's high) for each day that was strictly warmer than the previous calendar day. Sort by reading_date.
Starting Postgres in your browser…
Checking your access…