Churn rate has a simple formula and at least four common definitions, which is why two people in the same company routinely quote different figures for the same month.
This guide gives you both of the versions worth having, and explains which decision produced which number.
Customer churn
The share of customers you had at the start of a period who were gone by the end of it.
PostgreSQL
WITH bounds AS (
SELECT
DATE_TRUNC('month', CURRENT_DATE - INTERVAL '1 month') AS period_start,
DATE_TRUNC('month', CURRENT_DATE) AS period_end
)
SELECT
COUNT(*) FILTER (
WHERE s.canceled_at >= b.period_start
AND s.canceled_at < b.period_end
) AS churned,
COUNT(*) AS active_at_start,
ROUND(
100.0 * COUNT(*) FILTER (
WHERE s.canceled_at >= b.period_start
AND s.canceled_at < b.period_end
) / NULLIF(COUNT(*), 0),
2
) AS churn_rate_pct
FROM subscriptions s
CROSS JOIN bounds b
WHERE s.started_at < b.period_start
AND (s.canceled_at IS NULL OR s.canceled_at >= b.period_start);The WHERE clause is doing the important work: it restricts the population to subscriptions that already existed when the month began and had not yet been cancelled. That set is the denominator.
MySQL
MySQL has no FILTER, so the conditional counts become SUM(CASE ...):
SELECT
SUM(CASE WHEN canceled_at >= period_start
AND canceled_at < period_end THEN 1 ELSE 0 END) AS churned,
COUNT(*) AS active_at_start,
ROUND(
100.0 * SUM(CASE WHEN canceled_at >= period_start
AND canceled_at < period_end THEN 1 ELSE 0 END)
/ NULLIF(COUNT(*), 0), 2
) AS churn_rate_pct
FROM subscriptions
CROSS JOIN (
SELECT
DATE_FORMAT(CURDATE() - INTERVAL 1 MONTH, '%Y-%m-01') AS period_start,
DATE_FORMAT(CURDATE(), '%Y-%m-01') AS period_end
) b
WHERE started_at < b.period_start
AND (canceled_at IS NULL OR canceled_at >= b.period_start);Revenue churn
Customer churn counts heads. Revenue churn weighs them, and the two can point in opposite directions in the same month: lose ten small accounts and keep your largest, and customer churn looks terrible while revenue barely moves.
-- PostgreSQL
WITH bounds AS (
SELECT
DATE_TRUNC('month', CURRENT_DATE - INTERVAL '1 month') AS period_start,
DATE_TRUNC('month', CURRENT_DATE) AS period_end
)
SELECT
ROUND(
100.0 * SUM(
CASE WHEN s.canceled_at >= b.period_start
AND s.canceled_at < b.period_end
THEN s.amount ELSE 0 END
) / NULLIF(SUM(s.amount), 0),
2
) AS revenue_churn_pct
FROM subscriptions s
CROSS JOIN bounds b
WHERE s.started_at < b.period_start
AND (s.canceled_at IS NULL OR s.canceled_at >= b.period_start);Report both. A company that only watches one of them is flying with half an instrument panel.
Where the number goes wrong
Counting customers who joined during the period
This is the most common error, and it always understates churn. If someone signed up on the 10th and left on the 25th, including them in the denominator adds a customer you never really had for the period. The standard definition uses the population at the start of the period, which is what the started_at < period_start condition enforces.
Picking the denominator by feel
Start of period, end of period, or the average of the two: all three are used in practice, and they give different answers. Monthly churn of 3% against a starting base is not the same as 3% against an average base. The only real rule is to pick one and never change it, because churn is a trend and a redefinition erases the history.
Annualising a monthly rate by multiplying by twelve
Monthly churn of 5% is not 60% a year, it is about 46%, because each month churns what is left rather than the original base. The compounding version is 1 - POWER(1 - monthly_rate, 12). Multiplying by twelve is a shortcut that looks worse than reality, and it gets quoted in board meetings.
Treating failed payments as churn
A card that expires is not the same as a customer who left, and it usually recovers within days. If past_due and canceled live in the same status column, involuntary churn gets mixed into voluntary churn, and a fixable billing problem looks like a product problem.
Or just ask for it
These queries assume canceled_at marks the end of a subscription, that a null means still active, and that amount is comparable across plans. On a real schema the first job is finding out which columns actually carry that meaning.
ByeSQL reads your schema and writes the query against it, so “customer churn by month this year” comes back as a result with the SQL shown beside it, which is what lets you check the definition rather than assume it.