SQL Churn Rate
What fraction of your users have left?
What & Why
Churn rate is the mirror of conversion: COUNT(churned) / COUNT(total) * 100. Here, churned means churned_at IS NOT NULL.
See How It Works
BUSINESS QUESTION
For each signup month, compare churned users with all users in that cohort.
| id | created_at | country | channel | plan | activated_at | churned_at |
|---|---|---|---|---|---|---|
| 101 | 2024-01-15 09:10:00+00 | US | organic | pro | 2024-01-16 14:25:00+00 | NULL |
| 102 | 2024-02-10 11:05:00+00 | CA | paid | free | NULL | 2024-03-20 10:00:00+00 |
| 103 | 2024-03-12 16:35:00+00 | GB | referral | pro | 2024-03-13 08:15:00+00 | NULL |
| 104 | 2024-04-01 13:20:00+00 | US | organic | free | 2024-04-03 12:00:00+00 | NULL |
EXAMPLE QUERY
SELECT
DATE_TRUNC('month', created_at)::date AS signup_month,
COUNT(*) AS cohort_users,
COUNT(*) FILTER (WHERE churned_at IS NOT NULL) AS churned_users,
ROUND(COUNT(*) FILTER (WHERE churned_at IS NOT NULL)::numeric / NULLIF(COUNT(*), 0) * 100, 2) AS observed_churn_pct
FROM growth.users
GROUP BY signup_month
ORDER BY signup_month;RESULT — observed churn by signup month
| signup_month | cohort_users | churned_users | observed_churn_pct |
|---|---|---|---|
| 2024-01-01 | 1 | 0 | 0.00 |
| 2024-02-01 | 1 | 1 | 100.00 |
| 2024-03-01 | 1 | 0 | 0.00 |
| 2024-04-01 | 1 | 0 | 0.00 |
Only user 102 has a churn timestamp.
This lesson's practice is part of Pro.
Sign up free to try it on a real business scenario