Analytics Patterns · Anti-join across months · churn
55. Monthly churn rate
Concept: Anti-join across months · churnshow
A churned user was active last month and has no activity this month. You can't read churn off monthly totals, because new and returning users hide the ones who left. Build one row per user and month, LEFT JOIN each row to the same user's next month, and count the rows where nothing matched.
Example (different data):
SELECT p.season, COUNT(*) AS lapsed_members FROM memberships p LEFT JOIN memberships n ON n.member_id = p.member_id AND n.season = p.season + 1 WHERE n.member_id IS NULL GROUP BY p.season;
Your task
The retention team reports churn monthly: of the users active last month, how many didn't come back this month?
Using app_opens, for February, March and April 2024 return month (YYYY-MM), churned_users (users active in the previous month with no app opens this month) and churn_rate_pct (churned_users as a percentage of the previous month's active users, rounded to 1 decimal). Oldest first.
Starting Postgres in your browser…
Checking your access…