Practice · hard · dataset: ecom
Consecutive order-day streaks
An engagement report needs to know when customers ordered on consecutive calendar days. Find every run of two or more consecutive days on which a customer placed at least one order.
Return customer_id, streak_start (the first day of the run), streak_end (the last day), and
days (the number of consecutive days in the run). Sort by customer_id ascending, then
streak_start ascending. Ignore runs of a single day.
Tables available
| Table | Columns |
|---|---|
orders |
order_id, customer_id, order_date, status, channel |
Order status does not matter; any order counts. Multiple orders on the same day are still one day.
⌘/Ctrl + Enter to grade
Write a query, then grade it.
Hints
Try it first. Reveal a hint only when you are stuck.