Contraction MRR in SQL
Contents:
What contraction MRR actually measures
Contraction MRR is the slice of monthly recurring revenue you lose from customers who stayed. They did not churn. They did not freeze the credit card. They just pay you less than they did last month — they downgraded the plan, dropped a few seats, switched from annual to monthly, or capped a usage tier. The contract is still alive, but the line item shrank.
This is the metric a SaaS finance team at Stripe or Snowflake will pull when growth looks fine on the top line but net new MRR keeps disappointing the board. Gross adds and expansion can mask a steady leak from existing accounts. Contraction is the leak. If you do not measure it, you will keep blaming churn or sales targets for a problem that lives inside customer success and pricing.
Picture the scenario most analysts face. Your CFO walks over on a Monday morning and says the board wants a "movement of MRR" chart for the last six months — new business, expansion, contraction, and churn, broken out separately. You have a subscription_state table with daily snapshots. By lunch you need a query that returns those four numbers cleanly for any month, plus a sanity check that they add up to the actual change in MRR. The rest of this post is that query, the traps you will hit on the way, and how to defend the numbers when a PM challenges them in the review.
Contraction vs churn vs expansion
These three metrics describe the same population — existing customers — at the same checkpoint. The only thing that differs is direction and magnitude.
| Metric | What happened to the customer | What happened to MRR |
|---|---|---|
| Churn | Left entirely, subscription cancelled or expired | Went to zero |
| Contraction | Stayed, still active | Dropped but stayed greater than zero |
| Expansion | Stayed, still active | Grew |
The reason analysts confuse contraction with churn is reporting laziness. If a dashboard only tracks "MRR lost from existing customers", it lumps both buckets together. That number is useless for action. Churn means the relationship is broken — you need win-back, exit interviews, maybe a different ICP. Contraction means the relationship is intact but the customer renegotiated downward — you need pricing review, packaging, or proactive customer success outreach before next renewal. Two different problems, two different fixes.
The SQL recipe
We will start with the cleanest version: two snapshots of subscription state, one month apart, and a join that isolates customers whose MRR went down but did not hit zero. The pattern assumes you have a subscription_state table with one row per (customer_id, state_date) and a status flag.
WITH start_state AS (
SELECT customer_id, mrr AS start_mrr
FROM subscription_state
WHERE state_date = '2026-04-01' AND status = 'active'
),
end_state AS (
SELECT customer_id, mrr AS end_mrr
FROM subscription_state
WHERE state_date = '2026-05-01' AND status = 'active'
),
both AS (
SELECT
s.customer_id,
s.start_mrr,
e.end_mrr,
s.start_mrr - e.end_mrr AS contraction_amount
FROM start_state s
JOIN end_state e USING (customer_id)
WHERE e.end_mrr < s.start_mrr
AND e.end_mrr > 0
)
SELECT
COUNT(*) AS contracting_customers,
SUM(contraction_amount) AS total_contraction_mrr,
AVG(contraction_amount) AS avg_contraction_per_customer,
AVG(contraction_amount::NUMERIC / NULLIF(start_mrr, 0)) * 100 AS avg_pct_lost
FROM both;The inner JOIN ... USING (customer_id) is doing the heavy lifting. It silently drops anyone who appeared in start_state but not end_state — those are churned customers, and they belong in a different bucket. The explicit e.end_mrr > 0 is a defence in depth: if your status flag is unreliable and a churned customer still has a row with zero MRR, this filter catches it. The NULLIF on start_mrr is paranoia for free trials that were accidentally counted as active with zero MRR.
Run it on a month, eyeball the totals, then make sure the count of contracting customers plus expanding plus flat plus churned plus new equals your end-of-month customer base. If it does not balance, your snapshot table has gaps and the rest of the analysis will lie to you.
Splitting contraction by type
Aggregate contraction is a starting point. The real conversation begins when you split it by what changed. A plan downgrade tells a different story than a seat reduction, and both are different from a usage-tier drop.
WITH state_change AS (
SELECT
customer_id,
plan_at_start,
plan_at_end,
seats_at_start,
seats_at_end,
mrr_at_start,
mrr_at_end,
CASE
WHEN plan_at_end <> plan_at_start
AND mrr_at_end < mrr_at_start
THEN 'plan_downgrade'
WHEN seats_at_end < seats_at_start
AND plan_at_end = plan_at_start
THEN 'seat_reduction'
WHEN mrr_at_end < mrr_at_start
THEN 'usage_decline'
ELSE 'no_contraction'
END AS contraction_type
FROM monthly_customer_snapshot
)
SELECT
contraction_type,
COUNT(*) AS customers,
SUM(mrr_at_start - mrr_at_end) AS contraction_amount
FROM state_change
WHERE mrr_at_end < mrr_at_start
AND mrr_at_end > 0
GROUP BY contraction_type
ORDER BY contraction_amount DESC;Plan downgrades usually mean the customer judged that the higher tier was not worth the premium — packaging or feature value is the issue. Seat reductions tend to track headcount cuts on the customer side; in a soft macro environment, this bucket inflates first and most. Usage-tier declines are the most ambiguous category — the customer is using your product less, but you do not know whether that is seasonality, a workflow change, or the early warning of churn next quarter.
Net MRR movement in one query
Once you can compute each bucket, the natural next step is one query that returns new, expansion, contraction, and churn together with a window function that gives you the net new MRR. This is the chart the board actually wants.
WITH movement AS (
SELECT 'new' AS type,
SUM(start_mrr) AS amount
FROM new_subscriptions
WHERE month = '2026-04'
UNION ALL
SELECT 'expansion',
SUM(end_mrr - start_mrr)
FROM both
WHERE end_mrr > start_mrr
UNION ALL
SELECT 'contraction',
-SUM(start_mrr - end_mrr)
FROM both
WHERE end_mrr < start_mrr AND end_mrr > 0
UNION ALL
SELECT 'churn',
-SUM(start_mrr)
FROM churned_in_period
)
SELECT
type,
amount,
SUM(amount) OVER () AS net_new_mrr
FROM movement
ORDER BY CASE type
WHEN 'new' THEN 1
WHEN 'expansion' THEN 2
WHEN 'contraction' THEN 3
WHEN 'churn' THEN 4
END;Note the sign convention: contraction and churn are negative, expansion and new are positive. The SUM(amount) OVER () window adds them all up into a single net-new number repeated on every row, which is convenient for a BI tool that wants both the breakdown and the total in one result set.
Common mistakes
The first mistake is treating contraction as a flavor of churn. The customer is still paying you. Folding contraction into a single churn rate hides whether you have a relationship problem or a packaging problem. They take different playbooks to fix, and a board that sees the two collapsed into one number will ask the wrong questions.
The second mistake is forgetting that a pricing change is not contraction. If you cut list prices in April, every existing customer who renews at the new rate will look like contraction in your query — but the customer did not downgrade, you did. Always subtract a pricing-change cohort before reporting contraction, or annotate the chart so finance does not panic.
The third mistake is mixing voluntary and involuntary changes. A failed credit card that drops the customer to zero for one billing cycle and then recovers is not contraction. It is involuntary churn followed by reactivation, and it belongs in a separate dunning report. Filter your subscription_state to active-with-valid-payment before the join, or you will keep reporting noise as contraction.
The fourth mistake is averaging without a cohort dimension. An SMB customer on a one hundred dollar plan dropping a seat behaves nothing like an enterprise account on twenty thousand a month moving from Premium to Pro. Always group by ARR band or segment when you present the number; the unweighted average tells you almost nothing.
The fifth mistake is reporting only percentages or only absolute dollars. One enterprise customer losing fifty percent of MRR is a five-alarm fire. A hundred small accounts each losing ten percent might be background noise. Report both, and let the audience see which lens is doing the work.
Optimization tips
For most SaaS shops the contraction query runs over a few million rows and finishes in seconds, so optimisation is rarely the bottleneck. When it does matter — typically when you have years of daily snapshots and want a long historical chart — three things make the biggest difference.
Partition the subscription_state table by state_date and ensure the partition pruner is actually engaging. On Snowflake or BigQuery, check the query profile for partitions read; if it scanned the entire table to grab two days, you have a pruning bug. On Postgres, declarative partitioning with a RANGE on state_date and a compatible index on (state_date, customer_id) will get the planner there.
Materialize a monthly snapshot table that holds one row per customer per month-end. That table is two orders of magnitude smaller than the daily table and is the right granularity for nearly every revenue movement query. Rebuild it once a day; the contraction logic above runs against the monthly snapshot in milliseconds.
Avoid wide LEFT JOINs when you actually want an INNER JOIN. Some teams write the contraction query as a LEFT JOIN from start to end state and then filter end_mrr < start_mrr in the WHERE. That works but it scans churned customers unnecessarily. The INNER JOIN ... USING (customer_id) shape is both clearer and faster.
Related reading
- How to calculate churn in SQL
- How to calculate cohort retention in SQL
- How to calculate cohort revenue in SQL
- SQL window functions interview questions
If you want to drill SaaS revenue questions like this every day, NAILDD is launching with 500+ SQL problems across exactly this pattern.
FAQ
What is a healthy contraction MRR rate?
For SMB-heavy SaaS, five to fifteen percent of starting MRR per year is the usual band. Enterprise-heavy books should sit under five percent because the deals are larger, the procurement cycles longer, and a single downgrade is much more material. If you are above twenty percent for SMB or above ten for enterprise, treat contraction as a strategic problem and not a customer success ticket queue.
Does contraction count in churn rate?
It depends which churn rate. Revenue churn — gross or net — includes contraction by construction, since it measures dollars lost from existing customers. Logo churn does not, because the customer is still on the books. When you publish a single churn number, always say which one you mean and whether contraction is in or out.
How do I actually reduce contraction?
The first step is qualitative: short customer interviews with the accounts that downgraded, ideally within two weeks of the change. The pattern you are looking for is overscoping — you sold a Premium tier to an account whose actual workflow only needed the standard plan, and the customer eventually noticed. The fix is either packaging changes so the next account does not get oversold, or proactive outreach that anticipates the downgrade and offers a structured downsell that protects net retention.
Is contraction expected after launching a cheaper plan?
Yes, and that is healthy. When you introduce a lower-priced plan, some share of your existing base will move to it, especially the customers who were already price-sensitive. The metric to watch is net effect — whether the new plan brings in enough new customers to offset the dollars that migrated downward. If net is positive, contraction from the migration is doing exactly what the pricing change was designed to do.
Can contraction be stopped, or only managed?
It can be managed. You will never get contraction to zero in a real SaaS book because customer needs genuinely change. What you can do is build an early-warning system: a model that flags accounts whose usage trajectory predicts a downgrade ninety days out, and a customer success motion that intervenes with onboarding or value-realisation help before the renewal conversation turns adversarial.
How is contraction different from negative net dollar retention?
Negative net dollar retention is the outcome — the cohort shrank year over year — while contraction is one of the inputs that drove it there, alongside churn. A cohort can have heavy contraction and still post net dollar retention above one hundred percent if expansion is large enough to outpace it. When you brief a board, separate the components: NDR is the headline, contraction and churn are the failure modes, expansion is the engine.