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…