Engagement Streaks (3+ Orders, ≤7-Day Gaps)

Find customers whose ordering pattern shows engagement streaks — runs of 3 or more orders where every consecutive pair is at most 7 days apart. Order by streak_length descending, then customer_id. This is the gaps-and-islands pattern that powers session-style metrics at every e-commerce shop: when does a customer 'go hot' for a stretch?

For each qualifying streak, show customer_id, streak_start (first order_date in the run), streak_end (last order_date), and streak_length (count of orders in the streak).

The pattern (this is the interview answer): use LAG to get the previous order date per customer, flag a new streak whenever the gap exceeds 7 days, then SUM the flag as a running total — that running total IS the streak id. Group by (customer_id, streak_id) and HAVING COUNT >= 3.