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

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

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)

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

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:

-- 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 < b.subscription_month
WHERE c.subscription_month IS NULL  -- no rows in between
  AND b.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?"

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?"

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(DISTINCT customer_id) AS multi_reactivators
FROM same_year_events
GROUP BY customer_id, yr
HAVING COUNT(*) > 1;

Follow-up 3: "Performance — there are 100M subscription rows."

Index on (customer_id, subscription_month) — supports the partition.
Each CTE makes one pass. With the index, the ROW_NUMBER step is the cost; the rest are joins on already-grouped data.
For interactive use, pre-materialize the runs CTE as a daily refresh.