Per customer, return pairs of consecutive orders more than 5 days apart — the "lapses" in their purchasing. Include the START date (prior order's day), END date (current order's day), and the gap in days.
All order statuses count.
Sort by customer_id then gap_start_date ascending.
Returncustomer_idgap_start_dategap_end_dategap_days
About this problem
This problem practises the gaps half of gaps-and-islands: pairing each order with the same customer’s previous order and keeping only the pairs where the customer went quiet for more than five days. From orders, you report the start of the lapse (the earlier order’s day), the end (the later order’s day) and the gap in whole days. Every order counts regardless of status, because the question is about purchasing activity, not revenue. It needs LAG per customer_id in time order, date arithmetic on calendar days, and a filter that runs after the window has been computed.
The trap here is measuring the gap in the wrong unit. If you subtract the raw order_ts values you get intervals that include hours, and a pair of orders five days and one hour apart looks like “more than 5 days” when the calendar gap is exactly five. In the sample, c_001 has two pairs of orders exactly five calendar days apart, so this boundary decides real rows. A second mistake is filtering by status before LAG: dropping refunded, delivered or blank-status orders removes rows from the sequence and changes which orders are paired, so lapses appear, vanish or change length. Every pair still looks like a sensible gap, which is why the shift goes unnoticed. Finally, a window function cannot be filtered in the same WHERE clause that computes it, so a query that tries this fails before it returns anything.
Read the lesson: Gaps, Islands & Streaks (P19) →