Case 3 — Customers Who Churned and Reactivated in the Same Year

# Case 3 — Customers Who Churned and Reactivated in the Same Year > *"From the `subscriptions` table — one row per customer per month they were subscribed — find customers who churned and then reactivated within the same calendar year. Show the customer_id, the churn month, and the reactivation month."* The interviewer is hoping you see this is gaps-and-islands. If you start writing self-joins on adjacent months, you're going to spend 30 minutes building something that doesn't work for >2 gaps. --- ## Schema ``` subscriptions(customer_id, subscription_month) ``` `subscription_month` is the first of the month (`2026-03-01`). One row per (customer, month) when they had an active subscription. A "churn" is a month gap. A "reactivation" is the next subscription_month after a gap. ## The think-aloud > **You**: "OK, this is a gaps-and-islands problem. I need to identify contiguous runs of subscription months per customer, and then look at the boundaries between runs. Let me build it up." > **Interviewer**: *nods* ## Step a — Identify contiguous islands per customer ```sql WITH islands AS ( SELECT customer_id, subscription_month, subscription_month - (ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY subscription_month) * INTERVAL '1 month') AS island_key FROM subscriptions ) SELECT * FROM islands; ``` `subscription_month - row_num * 1 month` is constant within a contiguous run. Different islands = different keys. ## Step b — Find the start and end of each island ```sql WITH islands AS (...), runs AS ( SELECT customer_id, island_key, MIN(subscription_month) AS run_start, MAX(subscription_month) AS run_end FROM islands GROUP BY customer_id, island_key ) SELECT * FROM runs; ``` Each row is one continuous subscription period for one customer. ## Step c — For each customer, find consecutive runs (one ended, next started) ```sql WITH islands AS (...), runs AS (...), consecutive_runs AS ( SELECT customer_id, run_start, run_end, LEAD(run_start) OVER (PARTITION BY customer_id ORDER BY run_start) AS next_start, LEAD(run_end) OVER (PARTITION BY customer_id ORDER BY run_start) AS next_end FROM runs ) SELECT * FROM consecutive_runs WHERE next_start IS NOT NULL; ``` Each row pairs a (ended) run with the next reactivation. ## Step d — Filter to same-year churn-reactivation ```sql WITH islands AS (...), runs AS (...), consecutive_runs AS (...) SELECT customer_id, run_end + INTERVAL '1 month' AS churn_month, next_start AS reactivation_month FROM consecutive_runs WHERE next_start IS NOT NULL AND EXTRACT(YEAR FROM run_end) = EXTRACT(YEAR FROM next_start) ORDER BY customer_id, churn_month; ``` `run_end + 1 month` is the first month they were NOT subscribed (the churn month). `next_start` is the reactivation month. The `EXTRACT(YEAR) = EXTRACT(YEAR)` filter is the same-year constraint. ## Why this is hard the wrong way If you tried this with self-joins: ```sql -- DON'T DO THIS SELECT a.customer_id, a.subscription_month, b.subscription_month FROM subscriptions a JOIN subscriptions b ON b.customer_id = a.customer_id AND b.subscription_month > a.subscription_month LEFT JOIN subscriptions c ON c.customer_id = a.customer_id AND c.subscription_month > a.subscription_month AND c.subscription_month a.subscription_month + INTERVAL '1 month' -- gap of at least 1 month AND EXTRACT(YEAR FROM a.subscription_month) = EXTRACT(YEAR FROM b.subscription_month); ``` It works for single gaps but is fragile, slow, and a nightmare for the variant *"churned and reactivated multiple times in the year"*. The gaps-and-islands approach handles that case naturally — each consecutive run pair is one churn-reactivation event. ## What to say at the end > **You**: "The gaps-and-islands pattern is the right tool whenever the question has 'consecutive', 'streak', or 'gap' semantics. For this one, I built it as: identify islands, find consecutive runs per customer, filter to same-year boundaries. The runtime is O(N log N) — one pass over the data." ## Common follow-ups ### Follow-up 1: "What if I want the longest gap (months) between churn and reactivation, per customer?" ```sql WITH ... AS (...) -- previous CTEs SELECT customer_id, MAX(EXTRACT(MONTH FROM AGE(next_start, run_end))) AS longest_gap_months FROM consecutive_runs WHERE next_start IS NOT NULL GROUP BY customer_id; ``` ### Follow-up 2: "How many distinct customers churn-reactivated more than once in the same year?" ```sql WITH same_year_events AS ( SELECT customer_id, EXTRACT(YEAR FROM run_end) AS yr FROM consecutive_runs WHERE next_start IS NOT NULL AND EXTRACT(YEAR FROM run_end) = EXTRACT(YEAR FROM next_start) ) SELECT COUNT(DI