Approach: Define acquisition cohort by the month of a user’s first event (acquisition_month). For each cohort-month (cohort_month, calendar_month) mark whether the user is “churned” if they have no events in the 30 days after that calendar_month end. Compute per-user per-month churn flags then aggregate churn rate = churned / active_users_in_month.SQL (Postgres):sql
WITH user_first AS (
SELECT user_id,
date_trunc('month', MIN(event_timestamp))::date AS acquisition_month
FROM events
GROUP BY user_id
),
user_months AS (
-- all months from acquisition through last observed; generate per-user month rows
SELECT u.user_id, u.acquisition_month,
(date_trunc('month', m)::date) AS month_start
FROM user_first u
JOIN LATERAL (
SELECT generate_series(u.acquisition_month, date_trunc('month', now())::date, interval '1 month') AS m
) gen ON true
),
has_event AS (
-- whether user had any event within the calendar month
SELECT um.user_id, um.acquisition_month, um.month_start,
EXISTS (
SELECT 1 FROM events e
WHERE e.user_id = um.user_id
AND e.event_timestamp >= um.month_start
AND e.event_timestamp < (um.month_start + interval '1 month')
) AS active_in_month,
-- whether user had any event in 30 days after month end
EXISTS (
SELECT 1 FROM events e
WHERE e.user_id = um.user_id
AND e.event_timestamp >= (um.month_start + interval '1 month')
AND e.event_timestamp < (um.month_start + interval '1 month' + interval '30 days')
) AS active_in_30d_after
FROM user_months um
),
user_churn_flag AS (
SELECT user_id, acquisition_month, month_start,
active_in_month,
CASE
WHEN active_in_month = true AND active_in_30d_after = false THEN 1
ELSE 0
END AS churned
FROM has_event
)
SELECT acquisition_month,
month_start AS cohort_month,
COUNT(*) FILTER (WHERE active_in_month) AS active_users,
SUM(churned) AS churned_users,
ROUND(100.0 * SUM(churned) / NULLIF(COUNT(*) FILTER (WHERE active_in_month),0),2) AS churn_rate_pct
FROM user_churn_flag
GROUP BY acquisition_month, month_start
ORDER BY acquisition_month, month_start;
