Drill 3 of 12 in General questions
0

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…