Пометить каждого пользователя в каждом месяце как new, retained, churned или resurrected
Таблица activity(user_id, activity_date). Одним запросом классифицируйте каждого пользователя в каждом месяце как new (первый активный месяц), retained (активен в этом и прошлом), resurrected (активен в этом, неактивен в прошлом, но активен раньше) или churned (неактивен в этом, активен в прошлом).
-- на пользователя на месяц: new / retained / resurrected / churnedНапишите запрос.
Cross-join пользователей со шкалой месяцев и left-join активности дают флаг active на пользователя-месяц. LAG(active) даёт прошлый месяц, нарастающий SUM(active) — прошлые активности. Четыре метки жизненного цикла выводят, сравнивая флаг этого месяца с прошлым.
- ✗Метить по числу визитов текущего месяца без контекста прошлого месяца
- ✗Считать, что churn требует отдельного anti-join, ведь у него нет строки
- ✗Брать дневной разрыв событий вместо помесячного наличия
- →Как шкала месяцев позволяет классифицировать churned-месяц без строки?
- →Как из этих меток получить помесячную сводку growth accounting?
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.
