Label every user every month as new, retained, churned, or resurrected
Table activity(user_id, activity_date). In one query, classify every user in every month as new (first-ever active month), retained (active this month and last), resurrected (active this month, inactive last, but active earlier), or churned (inactive this month, active last month).
-- per user per month: new / retained / resurrected / churnedWrite the query.
Cross-join every user with a month spine, left-join activity for an active flag per user-month. LAG(active) gives last month; a running SUM(active) counts prior actives. The four lifecycle labels follow from comparing this month's flag to the previous month's.
- ✗Labeling from the current month's visit count without prior-month context
- ✗Believing churn needs a separate anti-join because it has no row
- ✗Using a day-level event gap instead of month-over-month presence
- →How does the month spine let a churned (row-less) month be classified?
- →How would you produce a monthly growth-accounting summary from these labels?
The churned state is a row that does not exist — a user silent this month after being active last month. So the query must first make every user-month explicit by cross-joining users with a month spine, then compare each month to the previous one:
WITH months AS (
SELECT generate_series(date_trunc('month', MIN(activity_date)),
date_trunc('month', MAX(activity_date)),
INTERVAL '1 month')::date AS month
FROM activity
),
user_active AS (
SELECT DISTINCT user_id, date_trunc('month', activity_date)::date AS month
FROM activity
),
grid AS (
SELECT u.user_id, m.month, (ua.user_id IS NOT NULL) AS active
FROM (SELECT DISTINCT user_id FROM activity) u
CROSS JOIN months m
LEFT JOIN user_active ua ON ua.user_id = u.user_id AND ua.month = m.month
),
seq AS (
SELECT user_id, month, active,
LAG(active) OVER (PARTITION BY user_id ORDER BY month) AS prev_active,
SUM(CASE WHEN active THEN 1 ELSE 0 END)
OVER (PARTITION BY user_id ORDER BY month) AS active_so_far
FROM grid
)
SELECT user_id, month,
CASE
WHEN active AND active_so_far = 1 THEN 'new'
WHEN active AND prev_active THEN 'retained'
WHEN active THEN 'resurrected'
WHEN prev_active THEN 'churned'
END AS state
FROM seq
WHERE active OR prev_active -- drop dormant (inactive after inactive) months
ORDER BY user_id, month;active_so_far = 1 identifies the first-ever active month (new); after that, prev_active splits retained from resurrected; and a churned month is NOT active AND prev_active, which only exists because the spine materialised the empty month. The CASE order matters — new must be tested before retained/resurrected. The final WHERE drops months where the user is dormant both this and last month, which carry no lifecycle transition.
