Churn Analysis in SQL: Monthly Logo and MRR Churn
Calculate monthly logo churn and MRR churn in SQL with the right denominator for each month. Runs on a free SaaS dataset, with counts reconciled to the source.
On this page · 7 sections
- Release
- v1.0.0
- Tool
- SQLite SQL
- Time
- 35 minutes
- Deliverables
- 4 build steps, 3 modeled assets
What this build produces.
A 24-row table for 2024 and 2025 with the subscriptions active at the start of each month, the cancellations in that month, and the logo and MRR churn rates, plus the same rates by plan.
Know what one row means.
- subscriptionsKey · subscription_id
- One row per subscription. In v1.0.0 every account has exactly one, with a start date and an end date that is empty while the subscription is active.
- subscription_monthsKey · month_start + subscription_id
- One row per subscription for each month in which it was active on the first day.
- monthly_churnKey · month
- One row per calendar month.
Fix the meaning before the code.
- Active at the start of a month
- The subscription started before the first day of the month and had not ended before that day. This is the denominator: only these subscriptions could churn in the month and be counted as lost from the opening base.
- Churned in a month
- The subscription was active at the start of the month and its ended_at date falls inside the month.
- Logo churn rate
- Subscriptions that churned in the month divided by subscriptions active at the start of the month. With one subscription per account, this is also the account churn rate.
- MRR churn rate
- The monthly recurring revenue of the subscriptions that churned divided by the MRR of all subscriptions active at the start of the month. This is gross MRR churn.
- Subscription-month
- One subscription active at the start of one month. Summing them over a year gives the denominator for an average monthly rate.
- Lifetime churn share
- Cancelled subscriptions divided by all subscriptions ever started. It is not a rate, because it has no time period, and it rises as a customer base ages.
Work from source grain to tested output.
- 01
Build a list of months and attach the active subscriptions
Grouping cancellations by month gives a numerator and nothing to divide it by. Start from a list of months and ask, for each month, which subscriptions were active on its first day.
sql · churn.sqlSubscriptions active at the start of each monthGenerate the 24 months of 2024 and 2025 with a recursive CTE, join each to the subscriptions active on its first day, and flag the ones that ended during the month.
23 linesCREATE TEMP VIEW subscription_months AS WITH RECURSIVE months(month_start) AS ( SELECT '2024-01-01' UNION ALL SELECT date(month_start, '+1 month') FROM months WHERE month_start < '2025-12-01' ) SELECT m.month_start, s.subscription_id, s.plan_id, s.mrr, CASE WHEN s.ended_at >= m.month_start AND s.ended_at < date(m.month_start, '+1 month') THEN 1 ELSE 0 END AS churned_in_month FROM months AS m INNER JOIN subscriptions AS s ON s.started_at < m.month_start AND (s.ended_at = '' OR s.ended_at >= m.month_start);sql · churn.sqlCheck the spineConfirm that every month is present and count the subscription-months.
5 linesSELECT COUNT(DISTINCT month_start) AS months, COUNT(*) AS subscription_months, SUM(churned_in_month) AS churned FROM subscription_months;Verification- On v1.0.0 the view covers 24 months and 72,051 subscription-months, with 894 cancellations.
- An active subscription has an empty string in ended_at, not NULL, so the join tests for '' explicitly.
- Dates are stored as YYYY-MM-DD text, which sorts in date order, so they can be compared as strings.
- 02
Calculate logo churn and MRR churn by month
Both rates share one denominator rule: what was active on the first day of the month. Logo churn counts subscriptions; MRR churn weights them by revenue.
sql · churn.sqlMonthly churnCollapse subscription-months to one row per month with both rates.
11 linesCREATE TEMP VIEW monthly_churn AS SELECT substr(month_start, 1, 7) AS month, COUNT(*) AS active_at_start, SUM(churned_in_month) AS churned, ROUND(100.0 * SUM(churned_in_month) / COUNT(*), 2) AS logo_churn_pct, SUM(mrr) AS mrr_at_start, SUM(mrr * churned_in_month) AS churned_mrr, ROUND(100.0 * SUM(mrr * churned_in_month) / SUM(mrr), 2) AS mrr_churn_pct FROM subscription_months GROUP BY month_start;sql · churn.sqlRead the resultReturn the 24 months in order.
3 linesSELECT * FROM monthly_churn ORDER BY month;sql · churn.sqlAverage monthly rate for 2025Divide the year's cancellations by the year's subscription-months. Averaging the twelve monthly percentages would weight a small month the same as a large one.
7 linesSELECT SUM(churned) AS churned, SUM(active_at_start) AS subscription_months, ROUND(100.0 * SUM(churned) / SUM(active_at_start), 2) AS avg_monthly_logo_churn_pct, ROUND(100.0 * SUM(churned_mrr) / SUM(mrr_at_start), 2) AS avg_monthly_mrr_churn_pct FROM monthly_churn WHERE month >= '2025-01';Verification- January 2025 opens with 3,103 active subscriptions; 43 of them cancel in the month, a logo churn rate of 1.39%.
- Monthly logo churn stays between 0.86% and 1.95% across the 24 months.
- For 2025 there are 511 cancellations over 44,032 subscription-months: an average monthly logo churn rate of 1.16%, and the same 1.16% for MRR churn.
- A 1.16% monthly rate compounds to about 13.1% over twelve months (1 minus 0.9884 to the power of 12), not 13.9%.
- The lifetime churn share for the same data is 19.3% (965 of 5,000 subscriptions). It answers a different question and should not be reported as a churn rate.
- 03
Compare churn by plan
A breakdown needs the same denominator rule inside each group: subscription-months on that plan, not all subscriptions on that plan.
sql · churn.sqlAverage monthly churn by plan, 2025Join the plan name and compute both rates per plan for 2025.
12 linesSELECT p.plan_name, COUNT(*) AS subscription_months, SUM(sm.churned_in_month) AS churned, ROUND(100.0 * SUM(sm.churned_in_month) / COUNT(*), 2) AS avg_monthly_logo_churn_pct, ROUND(100.0 * SUM(sm.mrr * sm.churned_in_month) / SUM(sm.mrr), 2) AS avg_monthly_mrr_churn_pct FROM subscription_months AS sm INNER JOIN plans AS p ON sm.plan_id = p.plan_id WHERE sm.month_start >= '2025-01-01' GROUP BY p.plan_name, p.monthly_price ORDER BY p.monthly_price;Verification- The four plans land between 1.10% and 1.20% a month: Starter 1.20%, Growth 1.10%, Scale 1.18%, Enterprise 1.19%.
- Enterprise has 28 cancellations in 2,358 subscription-months. One more or one fewer moves its rate by 0.04 points, so the gaps between plans here are not a finding.
- The churned counts across the four plans add up to the 511 cancellations in the 2025 total.
- 04
Test the grain and reconcile to the source
Each query returns zero rows when the model is right. The roll-forward test is the strongest: it proves that every month's opening count follows from the month before.
sqlOne row per subscription per monthReturn zero rows when no subscription is counted twice in a month.
7 linesSELECT month_start, subscription_id, COUNT(*) AS row_count FROM subscription_months GROUP BY month_start, subscription_id HAVING COUNT(*) > 1;sqlCancellations reconcile to the subscriptions tableReturn zero rows when the model counts every cancellation dated in 2024 or 2025 exactly once.
18 linesWITH model AS ( SELECT SUM(churned) AS churned FROM monthly_churn ), source AS ( SELECT COUNT(*) AS churned FROM subscriptions WHERE ended_at >= '2024-01-01' AND ended_at < '2026-01-01' ) SELECT model.churned AS model_churned, source.churned AS source_churned FROM model CROSS JOIN source WHERE model.churned <> source.churned;sqlOpening counts roll forwardReturn zero rows when each month's opening count equals the previous opening count, plus subscriptions started in that month, minus cancellations.
22 linesWITH started_by_month AS ( SELECT substr(started_at, 1, 7) AS month, COUNT(*) AS started FROM subscriptions GROUP BY substr(started_at, 1, 7) ), roll_forward AS ( SELECT c.month, c.active_at_start + COALESCE(s.started, 0) - c.churned AS expected_next_open, LEAD(c.active_at_start) OVER (ORDER BY c.month) AS actual_next_open FROM monthly_churn AS c LEFT JOIN started_by_month AS s ON c.month = s.month ) SELECT * FROM roll_forward WHERE actual_next_open IS NOT NULL AND expected_next_open <> actual_next_open;Verification- All three queries return zero rows on v1.0.0.
- The reconciliation compares 894 cancellations in the model with 894 in the subscriptions table.
- The roll-forward test works only because no subscription in this release starts and ends inside the same month. If one did, it would be a cancellation with no place in the opening count, and the churn definition would need a rule for it.
Release-backed output, not demo numbers.
walkthroughs.saas-churn-analysis-sql.monthly-churnTwenty-four months of opening subscriptions, cancellations, and logo and MRR churn rates.
monthactive_at_startchurnedlogo_churn_pctmrr_at_startchurned_mrrmrr_churn_pctChecked on 2026-11-21 against saas-subscriptions-v1.0.0.sqlite as published. First row: 2024-01, 1,667 active, 15 churned, 0.90% logo churn, 589,251 MRR at start, 4,257 churned MRR, 0.72% MRR churn. Last row: 2025-12, 4,079 active, 44 churned, 1.08%, 1,401,035, 13,594, 0.97%.
What this result does not claim.
- The data is synthetic and is not a benchmark for SaaS churn.
- Every account has one subscription and no plan changes, so there is no expansion or contraction revenue. Gross and net MRR churn are the same here; in real billing data they are not.
- The generator cancels subscriptions at the same rate on every plan and in every segment. Differences between groups in this release are chance, which makes it a good place to practice not over-reading a breakdown.
- No subscription cancels in its first 90 days and none that started after August 2025 cancels at all, so early-life churn cannot be studied with this release.
- ended_at is treated as the churn date. A billing system may separate the cancellation request from the end of the paid period; decide which one your metric uses.
- 2023 is left out because the business starts from zero that January and the opening counts are small.
- The temporary views need a SQLite connection that allows CREATE, such as the sqlite3 shell or DB Browser for SQLite on the downloaded file. The browser sandbox on this site runs SELECT statements only.
