SQL interview question: longest consecutive order-day streak
Problem
Shared data rulesFor each buyer, find the longest streak of consecutive days with orders. Return the buyers ranked 1–5 by streak length; equal streaks share a rank, and the rank isn’t an output column.
Formula
rank = 1 + number of buyers with a longer streak
Example
orders| order_id | user_id | status | created_at | Row note |
|---|---|---|---|---|
| 1 | 1 | delivered | 2026-08-10 12:00 | |
| 2 | 1 | delivered | 2026-08-11 09:30 | |
| 3 | 1 | delivered | 2026-08-12 11:00 | |
| 4 | 2 | delivered | 2026-08-14 10:00 | |
| 5 | 2 | delivered | 2026-08-15 13:00 | |
| 6 | 2 | delivered | 2026-08-15 18:00 | ← second order that day |
| 7 | 2 | delivered | 2026-08-16 13:00 |
- Order 6: second order that day
| user_id | start_day | end_day | days |
|---|---|---|---|
| 1 | 2026-08-10 | 2026-08-12 | 3 |
| 2 | 2026-08-14 | 2026-08-16 | 3 |
Both buyers have a 3-day streak — they share rank 1.
Notes
- If a buyer has several streaks of the maximum length, take the earliest.
Solve it free
Your query runs on PostgreSQL in your browser. No sign-up needed.