Skip to content
Dataset walkthrough

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
  1. Target outcome
  2. Grain contract
  3. Definitions
  4. Build sequence
  5. Expected result
  6. Limitations
  7. Dataset and exercise
Target outcome

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.

Grain contract

Know what one row means.

subscriptions
Key · 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_months
Key · month_start + subscription_id
One row per subscription for each month in which it was active on the first day.
monthly_churn
Key · month
One row per calendar month.
Definitions

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.
Build sequence

Work from source grain to tested output.

  1. 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.sql
    Subscriptions active at the start of each month

    Generate 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 lines
    CREATE 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.sql
    Check the spine

    Confirm that every month is present and count the subscription-months.

    5 lines
    SELECT
      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.
  2. 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.sql
    Monthly churn

    Collapse subscription-months to one row per month with both rates.

    11 lines
    CREATE 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.sql
    Read the result

    Return the 24 months in order.

    3 lines
    SELECT *
    FROM monthly_churn
    ORDER BY month;
    sql · churn.sql
    Average monthly rate for 2025

    Divide 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 lines
    SELECT
      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.
  3. 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.sql
    Average monthly churn by plan, 2025

    Join the plan name and compute both rates per plan for 2025.

    12 lines
    SELECT
      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.
  4. 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.

    sql
    One row per subscription per month

    Return zero rows when no subscription is counted twice in a month.

    7 lines
    SELECT
      month_start,
      subscription_id,
      COUNT(*) AS row_count
    FROM subscription_months
    GROUP BY month_start, subscription_id
    HAVING COUNT(*) > 1;
    sql
    Cancellations reconcile to the subscriptions table

    Return zero rows when the model counts every cancellation dated in 2024 or 2025 exactly once.

    18 lines
    WITH 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;
    sql
    Opening counts roll forward

    Return zero rows when each month's opening count equals the previous opening count, plus subscriptions started in that month, minus cancellations.

    22 lines
    WITH 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.
Expected result

Release-backed output, not demo numbers.

v1.0.0 · verified output
walkthroughs.saas-churn-analysis-sql.monthly-churn

Twenty-four months of opening subscriptions, cancellations, and logo and MRR churn rates.

Columns
monthactive_at_startchurnedlogo_churn_pctmrr_at_startchurned_mrrmrr_churn_pct

Checked 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%.

Limitations

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.
Continue with the data

Open the matching dataset and exercise.