Drill 12 of 12 in Interview Classics
0

Interview Classics · Self-join on date + 1 · consecutive-day trends

12. Three days of rising sales

Concept: Self-join on date + 1 · consecutive-day trendsshow

To compare a row with the next calendar days, self-join on the date: JOIN t d2 ON d2.key = d1.key AND d2.day = d1.day + 1. Unlike LAG, which just reads the previous row, a date join can't skip over a missing day. Chain a third alias for day + 2 and compare the values with a strict >.

Example (different data):

SELECT a.city, a.day
FROM temps a
JOIN temps b ON b.city = a.city AND b.day = a.day + 1
WHERE b.high < a.high;

Your task

Classic interview question: operations wants every stretch where a coffee kiosk's sales rose three days running, to see what drove the momentum.

Find every run of three consecutive calendar days at the same kiosk where cups_sold strictly increased from day 1 to day 2 and from day 2 to day 3. Overlapping runs each count, and a closed day (no row) breaks a run. Return kiosk, start_date (day 1) and end_date (day 3). Sort by kiosk, start_date.

Starting Postgres in your browser…

Checking your access…