0Pricing
SQL Interview Prep · Lesson

Churn and Resurrection Queries

Identifying users who left and those who returned after a gap.

Churn and Resurrection Queries is a free SQL Interview Prep lesson on CoddyKit — lesson 4 of 4. You can read the complete lesson below for free — then practise it hands-on in the browser with a built-in code editor and a 24/7 AI tutor. It is part of the SQL Interview Prep learning path, one of 4 lessons in the course, and your progress syncs across the web and the CoddyKit app.

The Flip Side of Retention

If retention measures who stayed, churn measures who left, and resurrection measures who came back. Interviewers pair these with retention because they reveal whether you can reason about absence of activity, which is harder than counting presence.

The recurring trick: you cannot filter on rows that do not exist. Churn queries are fundamentally about finding the gap between a user's last activity and now (or their next activity).

Defining Churn Precisely

"Churned" is meaningless without a window. A common definition: a user is churned if they have had no activity in the last 30 days. The 30-day inactivity threshold is the business choice you must pin down.

For subscription products, churn may instead mean a cancelled or expired subscription, a status change rather than an activity gap. Clarify which model applies before writing SQL.

Last Activity Per User

The foundation of activity-gap churn is each user's most recent event. Group by user and take MAX of the event date.

This single value, compared against today, tells you how long the user has been silent. Everything downstream is a comparison against this last-seen date.

SELECT
  user_id,
  MAX(event_at::date) AS last_active
FROM events
GROUP BY user_id;

The Churned-Users Query

A user is churned if their last activity is more than 30 days ago. Compare last_active to CURRENT_DATE - 30. Anyone whose most recent event predates that cutoff has gone quiet.

Notice the work happens after aggregation: you reduce to one row per user, then test the gap. Filtering raw events by date would only tell you who was inactive in a window, not who is overall churned.

WITH last_seen AS (
  SELECT user_id, MAX(event_at::date) AS last_active
  FROM events
  GROUP BY user_id
)
SELECT user_id, last_active
FROM last_seen
WHERE last_active < CURRENT_DATE - INTERVAL '30 days';

Counting Churn Rate

Churn rate is churned users over the relevant base, often users who were active at the start of the period. Use conditional aggregation to count churned and total in one pass, then divide carefully with 100.0 and NULLIF.

Be explicit about the denominator in the interview: churn over all-time users versus churn over previously-active users are different metrics.

WITH last_seen AS (
  SELECT user_id, MAX(event_at::date) AS last_active
  FROM events GROUP BY user_id
)
SELECT
  COUNT(*) FILTER (
    WHERE last_active < CURRENT_DATE - INTERVAL '30 days'
  ) AS churned,
  COUNT(*) AS total_users,
  ROUND(100.0 * COUNT(*) FILTER (
    WHERE last_active < CURRENT_DATE - INTERVAL '30 days')
    / NULLIF(COUNT(*), 0), 1) AS churn_pct
FROM last_seen;

Period-Over-Period Churn With Set Logic

Another framing: who was active last month but not this month? This is a set difference. Build the set of last-month active users and the set of this-month active users, then find members of the first not in the second.

You can express it with EXCEPT, a LEFT JOIN / IS NULL anti-join, or NOT EXISTS. The anti-join is the most portable and the one interviewers most often want to see.

WITH last_month AS (
  SELECT DISTINCT user_id FROM events
  WHERE event_at >= DATE '2024-04-01' AND event_at < DATE '2024-05-01'
),
this_month AS (
  SELECT DISTINCT user_id FROM events
  WHERE event_at >= DATE '2024-05-01' AND event_at < DATE '2024-06-01'
)
SELECT user_id FROM last_month
EXCEPT
SELECT user_id FROM this_month;

The Anti-Join Form

The same churn-this-period query as an anti-join: LEFT JOIN this month's actives onto last month's, then keep rows where the match is NULL. These are users present last month but absent this month, the churners.

NOT EXISTS is an equally good answer and handles NULLs safely. Mention that NOT IN would be risky if the inner set could contain NULLs, a classic gotcha.

SELECT lm.user_id
FROM last_month lm
LEFT JOIN this_month tm ON tm.user_id = lm.user_id
WHERE tm.user_id IS NULL;

Defining Resurrection

Resurrection (a.k.a. reactivation) is a user who was churned and then became active again. The signature is a gap in their timeline: active, then a stretch of silence longer than the churn threshold, then active again.

So a resurrected user this month is one who is active now, was inactive last period, but had activity in some earlier period. It is the mirror image of churn.

Detecting Gaps With LAG

The elegant way to find resurrection is the LAG window function: for each activity period per user, look at the previous active period. If the gap between them exceeds the threshold, this period is a reactivation.

LAG avoids a self-join and reads cleanly. Partition by user, order by the active period, and compare each period to its predecessor.

WITH monthly AS (
  SELECT DISTINCT user_id,
    DATE_TRUNC('month', event_at) AS active_month
  FROM events
),
gaps AS (
  SELECT user_id, active_month,
    LAG(active_month) OVER (
      PARTITION BY user_id ORDER BY active_month
    ) AS prev_month
  FROM monthly
)
SELECT user_id, active_month AS resurrected_month
FROM gaps
WHERE prev_month IS NOT NULL
  AND active_month > prev_month + INTERVAL '1 month';

New vs Resurrected vs Retained

A complete activity-classification query labels every active user this period as one of: new (no prior activity), retained (active last period too), or resurrected (prior activity but a gap). The prev_month from LAG drives all three.

  • prev_month IS NULL → new
  • prev_month = active_month - 1 → retained
  • otherwise (a gap) → resurrected

Producing this breakdown is a strong, complete answer.

SELECT user_id, active_month,
  CASE
    WHEN prev_month IS NULL THEN 'new'
    WHEN active_month = prev_month + INTERVAL '1 month' THEN 'retained'
    ELSE 'resurrected'
  END AS user_state
FROM gaps;

The NOT IN With NULL Trap

A final landmine. If you write churn as WHERE user_id NOT IN (SELECT user_id FROM this_month) and that subquery returns even one NULL, the entire result becomes empty, because NOT IN evaluates to UNKNOWN against NULL.

Prefer NOT EXISTS or a LEFT JOIN / IS NULL anti-join, which behave correctly with NULLs. Calling out this difference unprompted is a reliable senior signal in retention interviews.

-- safe anti-join instead of NOT IN
SELECT lm.user_id
FROM last_month lm
WHERE NOT EXISTS (
  SELECT 1 FROM this_month tm
  WHERE tm.user_id = lm.user_id
);

Quick Check

You want users active last month but not this month. A teammate wrote WHERE user_id NOT IN (SELECT user_id FROM this_month) and it returns zero rows even though some clearly churned. What is the safest fix?

Recap: Churn and Resurrection

Churn and resurrection essentials:

  • Define churn by an inactivity threshold (e.g., no activity in 30 days) or a subscription status change — clarify which.
  • Compute each user's MAX(last activity), then compare to CURRENT_DATE - threshold.
  • Period-over-period churn is a set difference: use EXCEPT, NOT EXISTS, or a LEFT JOIN / IS NULL anti-join.
  • Resurrection is a timeline gap; detect it with LAG to classify users as new / retained / resurrected.
  • Avoid NOT IN when NULLs are possible — it silently empties the result.

Frequently asked questions

Is the “Churn and Resurrection Queries” lesson free?

Yes — the full text of “Churn and Resurrection Queries” is free to read here on the web, and the SQL Interview Prep course includes 4 lessons in total. To practise it interactively (a built-in code editor and a 24/7 AI tutor) and unlock the rest of the SQL Interview Prep course, upgrade to CoddyKit PRO.

What will I learn in “Churn and Resurrection Queries”?

Identifying users who left and those who returned after a gap. You practise SQL Interview Prep with hands-on code you run directly in the browser, and a 24/7 AI tutor answers your questions as you work through the lesson.

Do I need any experience to start SQL Interview Prep?

No prior experience is required. SQL Interview Prep on CoddyKit is structured for beginners through advanced learners; this is — lesson 4 of 4, so you can start here or from the beginning and move at your own pace.

How long does the “Churn and Resurrection Queries” lesson take?

Most CoddyKit lessons take about 5–10 minutes. Each one is bite-sized and interactive, so you make steady progress and pick up exactly where you left off across the web and the app.

Can I write and run code in this SQL Interview Prep lesson?

Yes. Every SQL Interview Prep lesson includes a built-in code editor, so you write and run real code right in your browser and get instant AI feedback — no local setup required.

All lessons in this course

  1. Defining a Cohort by First Action
  2. Building a Retention Matrix
  3. Day-N and Rolling Retention
  4. Churn and Resurrection Queries
← Back to SQL Interview Prep