Longest Consecutive Order Streak Per Customer
Preview mode. Log in to edit, run, submit, and save progress.
Description
You are given an Orders table. Each row records a customer ID and an order date. A customer may have multiple orders on the same day. Write a SQL query to find the longest streak of consecutive calendar days on which each customer placed at least one order. Duplicate order dates for the same customer count as a single active day. Return customer_id and longest_streak, ordered by customer_id. Table: Orders
| Column Name | Type | Description |
|---|---|---|
| order_id | INT | Primary key |
| customer_id | INT | ID of the customer |
| order_date | DATE | Date the order was placed |
Database Schema (Inferred)
Orders
| Column Name | Example Value |
|---|---|
| order_id | 1 |
| customer_id | 1 |
| order_date | 2023-01-01 |
Example
Orders
| order_id | customer_id | order_date |
|---|---|---|
| 1 | 1 | 2023-01-01 |
| 2 | 1 | 2023-01-02 |
| 3 | 1 | 2023-01-03 |
| 4 | 1 | 2023-01-05 |
| 5 | 2 | 2023-02-10 |
| 6 | 2 | 2023-02-11 |
| 7 | 3 | 2023-03-01 |
| 8 | 3 | 2023-03-02 |
| 9 | 3 | 2023-03-03 |
| 10 | 3 | 2023-03-04 |
| 11 | 3 | 2023-03-05 |
Output
| customer_id | longest_streak |
|---|---|
| 1 | 3 |
| 2 | 2 |
| 3 | 5 |
Explanation:
Customer 1 has Jan 1-3 (streak=3) then a gap on Jan 4. Customer 2 has 2 consecutive days. Customer 3 has 5 consecutive days.
Approach hint
Start with a simple approach, explain the trade-off, then move toward a cleaner or more scalable solution.
Common mistake
Skipping assumptions, edge cases, or trade-offs can make an otherwise good answer feel incomplete.
Orders
| order_id | customer_id | order_date |
|---|---|---|
| 1 | 1 | 2023-01-01 |
| 2 | 1 | 2023-01-02 |
| 3 | 1 | 2023-01-03 |
| 4 | 1 | 2023-01-05 |
| 5 | 2 | 2023-02-10 |
| 6 | 2 | 2023-02-11 |
| 7 | 3 | 2023-03-01 |
| 8 | 3 | 2023-03-02 |
| 9 | 3 | 2023-03-03 |
| 10 | 3 | 2023-03-04 |
| 11 | 3 | 2023-03-05 |
Output
| customer_id | longest_streak |
|---|---|
| 1 | 3 |
| 2 | 2 |
| 3 | 5 |