Loyalty programs often reward customers who order every month without a gap. To identify those customers, the growth team needs to find the longest unbroken run of consecutive calendar months in which each customer placed at least one order. Using the orders table, find each customer's longest streak of consecutive calendar months with at least one order — return customer_id and longest_streak, ordered by streak descending then customer_id.
orders
| column | type |
|---|---|
| id | INTEGER |
| customer_id | INTEGER |
| amount | NUMERIC |
| created_at | DATE |
orders
| id | customer_id | amount | created_at |
|---|---|---|---|
| 1 | 1 | 100.00 | 2024-01-10 |
| 2 | 1 | 50.00 | 2024-01-25 |
| 3 | 1 | 75.00 | 2024-02-14 |
| 4 | 1 | 200.00 | 2024-03-05 |
| 5 | 1 | 60.00 | 2024-05-20 |
| 6 | 2 | 90.00 | 2024-01-08 |
| 7 | 2 | 110.00 | 2024-02-22 |
| 8 | 2 | 40.00 | 2024-04-11 |
| 9 | 2 | 80.00 | 2024-05-30 |
| customer_id | longest_streak |
|---|---|
| 1 | 3 |
| 2 | 2 |
Customer 1 ordered in Jan, Feb, and Mar consecutively (streak = 3), then again in May alone (streak = 1). Customer 2 ordered in Jan and Feb (streak = 2), skipped March, then ordered in Apr and May (streak = 2).