Calculating Churn Rate in SQL (Monthly and Revenue)

Churn is one of those metrics where the SQL is easy and the definition is a minefield. Two people can both write a correct query and get different numbers, because they quietly disagreed on what churn means. So before any SQL, the definitions, because that's where the mistakes actually live.

Customer churn vs revenue churn

Customer churn is the percentage of customers who left during a period. Lose 5 of 100 customers this month, that's 5% customer churn.

Revenue churn is the percentage of recurring revenue you lost. Those 5 customers might have been your smallest accounts (revenue churn below 5%) or your biggest (revenue churn way above 5%). For most subscription businesses, revenue churn is the number that actually predicts whether you live, because losing one enterprise account hurts more than losing ten hobbyists.

Compute both. They tell different stories and you want both stories.

Customer churn

Assume a subscriptions table with customer_id, started_at, and canceled_at (null if still active). Monthly customer churn is: of the customers active at the start of the month, how many canceled during it.

with month_bounds as (
  select date_trunc('month', now()) as month_start,
         date_trunc('month', now()) + interval '1 month' as month_end
),
active_at_start as (
  select count(distinct customer_id) as n
  from subscriptions, month_bounds
  where started_at < month_start
    and (canceled_at is null or canceled_at >= month_start)
),
churned as (
  select count(distinct customer_id) as n
  from subscriptions, month_bounds
  where canceled_at >= month_start
    and canceled_at < month_end
)
select
  active_at_start.n as customers_at_start,
  churned.n as customers_churned,
  round(100.0 * churned.n / nullif(active_at_start.n, 0), 2) as churn_pct
from active_at_start, churned;

The denominator is the catch. Churn is over customers who were active at the start of the period, not your total customer count and not the end count. Get the denominator wrong and your churn rate is wrong in a direction that usually flatters you.

Revenue churn

Same idea, but sum the monthly recurring revenue of the customers who left instead of counting them. Assume each subscription has an mrr amount.

with month_bounds as (
  select date_trunc('month', now()) as month_start,
         date_trunc('month', now()) + interval '1 month' as month_end
),
mrr_at_start as (
  select coalesce(sum(mrr), 0) as total
  from subscriptions, month_bounds
  where started_at < month_start
    and (canceled_at is null or canceled_at >= month_start)
),
churned_mrr as (
  select coalesce(sum(mrr), 0) as total
  from subscriptions, month_bounds
  where canceled_at >= month_start
    and canceled_at < month_end
)
select
  mrr_at_start.total as mrr_at_start,
  churned_mrr.total as mrr_churned,
  round(100.0 * churned_mrr.total / nullif(mrr_at_start.total, 0), 2) as revenue_churn_pct
from mrr_at_start, churned_mrr;

The thing this simple version ignores

The queries above measure gross churn — revenue and customers lost — and they ignore expansion. If your remaining customers upgraded during the month, your net revenue churn could be much lower than gross, and for some businesses it's even negative (expansion outpaces losses, which is the dream). If net revenue retention matters to you, you need to add upgrades and downgrades to the calculation, which means tracking MRR changes over time, not just cancellations.

I'd start with gross. It's honest about how many people are leaving, and net churn can hide a real retention problem behind a few big upgrades. Look at both eventually, but don't let a healthy net number talk you out of fixing a gross churn problem that's quietly bleeding customers.

A note on annual plans

If you mix monthly and annual subscriptions, "monthly churn" gets slippery, because an annual customer can't churn in most months by definition. Normalize to a consistent MRR (annual price divided by 12) and decide whether you're measuring when they cancel or when their term actually ends. Those are different events and they can land in different months. Pick one and be consistent, or your churn chart will have phantom spikes.

These queries run fine in plain psql. If you'd rather just ask "what was our revenue churn last month" and read the SQL back to sanity-check the denominator, that's the QueryBear workflow, but the math is the math either way. Get the definition right first. The query is the easy part.