Expansion MRR in SQL

What expansion MRR actually measures

Expansion MRR is the slice of monthly recurring revenue from customers you already have. They were on the books last month, they are still on the books this month, and the line item on their invoice got bigger — they bumped from Pro to Business, added eleven seats, or their API usage tier ratcheted up because the integration they shipped in March actually worked.

This is the metric finance teams at Snowflake, Stripe, or Notion stare at when growth looks soft on new logos. New business is expensive — paid acquisition, BDR teams, six-week sales cycles. Expansion is the cheapest dollar in SaaS: the customer already chose you, already integrated, already trained their team. A healthy product-led book pulls thirty to fifty percent of all new MRR out of expansion every quarter.

Picture the Monday morning. Your head of revenue wants a clean expansion number for April — total dollars, accounts contributing, and a split by plan upgrades, seat additions, or usage. You have a subscription_state table with daily snapshots. By lunch you need queries plus a sanity check that expansion plus new minus contraction minus churn equals the actual change in MRR. The rest of this post is those queries and the traps you hit on the way.

Three flavors of expansion

Plan upgrade is the classic — a customer moves from Basic to Premium, Pro to Business. The MRR jump is discrete and usually large. When plan upgrades dominate the mix, packaging is working: customers are hitting the ceiling and choosing to pay for the next tier.

Seat expansion is the headcount lever. The customer keeps the same plan but adds users. This is the dominant pattern at any company selling to teams — Linear, Figma, Vercel, Notion. The delta is smaller per event but happens far more often. When seat expansion drives the curve, the product has crossed from one champion to a real team workflow.

Usage-based growth is the API meter ticking up. Customers on consumption plans — Snowflake credits, OpenAI tokens, Stripe processing volume — pay more this month because they did more this month. Usage tracks the customer's own business momentum, which is the strongest foundation but also the most exposed to their downturns. A healthy book shows all three.

The SQL recipe

We will start with the cleanest version: two snapshots a month apart, joined to isolate customers whose MRR went up. The pattern assumes a subscription_state table with one row per (customer_id, state_date) and a status flag — the shape most modern billing pipelines materialize from Stripe or a downstream warehouse model.

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,
        GREATEST(0, e.end_mrr - s.start_mrr) AS expansion_amount
    FROM start_state s
    JOIN end_state e USING (customer_id)
)
SELECT
    COUNT(*) FILTER (WHERE expansion_amount > 0) AS expanding_customers,
    SUM(expansion_amount) AS total_expansion_mrr,
    AVG(expansion_amount) FILTER (WHERE expansion_amount > 0) AS avg_expansion_per_customer,
    AVG(expansion_amount::NUMERIC / NULLIF(start_mrr, 0))
        FILTER (WHERE expansion_amount > 0) * 100 AS avg_pct_growth
FROM both;

The inner JOIN ... USING (customer_id) is doing the heavy lifting. It silently drops anyone who appeared only in end_state — those are new customers, and they belong in the new business bucket. It also drops anyone who appeared only in start_state — those are churned. What survives is the cohort that was active on both anchor dates, which is the only place expansion can actually live.

The FILTER (WHERE expansion_amount > 0) clauses avoid polluting the average with zeros and contracting accounts. Without them, avg_expansion_per_customer divides total expansion by the entire active base and returns a number ten times smaller than what is useful for narrative. Always report average growth conditional on having grown.

Run it on a month, eyeball the totals, then check that expanding plus contracting 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 expansion by type

Aggregate expansion is a starting point. The real conversation begins when you split by what changed — a plan upgrade tells a different story than a seat addition, and both differ from a usage tier increase.

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_upgrade'
            WHEN seats_at_end > seats_at_start
                 AND plan_at_end = plan_at_start
                THEN 'seat_expansion'
            WHEN mrr_at_end > mrr_at_start
                THEN 'usage_growth'
            ELSE 'no_expansion'
        END AS expansion_type
    FROM monthly_customer_snapshot
    WHERE snapshot_month = '2026-04-01'
)
SELECT
    expansion_type,
    COUNT(*) AS customers,
    SUM(mrr_at_end - mrr_at_start) AS expansion_mrr,
    AVG(mrr_at_end - mrr_at_start) AS avg_delta
FROM state_change
WHERE mrr_at_end > mrr_at_start
GROUP BY expansion_type
ORDER BY expansion_mrr DESC;

Plan upgrades mean the customer hit a feature ceiling — packaging is doing its job. Seat expansion says the product has spread from one champion to a team workflow; at a tool like Notion or Figma in a healthy quarter, this category dominates. Usage growth is the most ambiguous because the customer made no decision — your meter ticked up. Treat usage spikes as a leading indicator rather than a strategic lever.

When the CASE above sees a customer who both upgraded the plan and added seats, it labels them plan_upgrade because that branch evaluates first. That priority is intentional — the higher-revenue event wins the label — but agree on it explicitly with finance so two analysts get the same answer.

Net new MRR in one query

Once expansion is isolated, the next step is one query that returns new, expansion, contraction, and churn together with a window function for net new MRR. This is the chart the board actually wants.

WITH movement AS (
    SELECT 'new'         AS type,
           SUM(mrr)       AS amount
    FROM subscriptions
    WHERE created_at >= '2026-04-01'
      AND created_at <  '2026-05-01'
    UNION ALL
    SELECT 'expansion',
           SUM(expansion_amount)
    FROM both
    WHERE expansion_amount > 0
    UNION ALL
    SELECT 'contraction',
           -SUM(s.start_mrr - COALESCE(e.end_mrr, 0))
    FROM start_state s
    LEFT JOIN end_state e USING (customer_id)
    WHERE COALESCE(e.end_mrr, 0) < s.start_mrr
      AND COALESCE(e.end_mrr, 0) > 0
    UNION ALL
    SELECT 'churn',
           -SUM(s.start_mrr)
    FROM start_state s
    LEFT JOIN end_state e USING (customer_id)
    WHERE e.end_mrr IS NULL OR e.end_mrr = 0
)
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;

Contraction and churn are negative, expansion and new are positive. The SUM(amount) OVER () adds them into a single net-new number repeated on every row, convenient for BI tools that want both the breakdown and the total in one result set. The first time you wire this up, validate against the raw MRR delta — end-of-period minus start-of-period — and confirm the two agree to the dollar.

Common pitfalls

The first pitfall is counting a win-back as expansion. A customer who churned in February, came back in April on a larger plan, and now contributes more MRR is not expanding — they are a new sale on top of a recovered logo. Require the customer to be active on both anchor dates before the join, which is what the INNER JOIN ... USING (customer_id) above does for free. With a LEFT JOIN, add an explicit filter requiring start_mrr > 0 and end_mrr > 0.

The second pitfall is reading expansion off invoice posts instead of subscription state. A customer who paid annual upfront in March will look like a giant expansion event in March and a giant contraction in April when nothing posts. Always normalize to monthly recurring on the subscription state, never on the invoice ledger; the metric is recurring revenue, not cash.

The third pitfall is missing a downgrade-then-upgrade path inside one period. A customer who briefly dropped from Premium to Pro and returned to Premium in the same month shows zero net change at the boundary. That round trip matters for retention narrative — catch it with weekly snapshots or by replaying an event log.

The fourth pitfall is treating a price increase as voluntary expansion. If list prices went up in April, every existing customer renewing at the new rate inflates the expansion number — but the customer did not choose to spend more, you raised the rate. Carve a pricing-change cohort out, or annotate the chart so finance does not high-five the wrong team.

The fifth pitfall is averaging expansion across the whole base instead of the population that actually expanded. Report two numbers: average expansion across expanding accounts, and percentage of base that expanded. Either alone is misleading.

Optimization tips

For most SaaS shops the expansion query runs over a few million rows and finishes in seconds, so optimization is rarely the bottleneck. When it does matter — typically with years of daily snapshots feeding a long historical chart — three things help most.

Partition the subscription_state table by state_date and ensure the pruner is 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 plus an 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. It 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 same table then powers expansion, contraction, churn, and net dollar retention without extra plumbing.

Avoid wide LEFT JOINs when you actually want an INNER JOIN. Some teams write expansion as a LEFT JOIN from start to end state and filter end_mrr > start_mrr in the WHERE. That works but scans new and churned customers only to discard them. The INNER JOIN ... USING (customer_id) shape is clearer and faster, and it documents intent: expansion lives only inside the surviving cohort.

Related reading

• How to calculate contraction MRR in SQL
• How to calculate churn in SQL
• How to calculate ARR growth 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 share of new MRR should come from expansion?

Thirty to fifty percent is the band most healthy SaaS businesses sit in, with product-led companies like Notion, Figma, or Vercel often pushing past sixty percent once the team-adoption flywheel kicks in. Below twenty percent, packaging is leaving money on the table or the ICP is too small per account to expand meaningfully. Above seventy percent, growth is becoming dependent on existing customers and a single large account churning will leave a hole new business cannot fill quickly.

Is expansion MRR the same as upsell?

For analytics purposes, yes. Sales teams use "upsell" for a deliberate motion — a deal a rep closed against an account they manage. Analysts use "expansion" because it counts every dollar of growth from existing customers regardless of whether a human pushed for it. The numbers overlap in a sales-led company and diverge in a product-led one. When you publish a number, say which lens you are using so a VP of Sales does not claim credit for self-serve seat additions.

Can expansion MRR be negative?

No. Negative movement among existing customers is contraction — a different metric with a different playbook. In the net-new MRR query above, contraction is reported as a negative number alongside expansion so the four buckets sum to the total change, but the expansion total itself is always non-negative. A negative expansion figure means the bucket boundaries are broken.

How does expansion feed into quick ratio?

SaaS quick ratio is (new + expansion) / (contraction + churn). A ratio of four or higher is considered strong — for every dollar leaving, four are arriving. Quick ratio is more diagnostic than net new MRR because it normalizes for scale: a small fast-growing company can have a worse net new number than a large slow one while the quick ratio correctly favors the small company. Track it monthly and trend it; absolute levels matter less than the shape of the curve.

Do I need warehouse snapshots, or can I work off raw subscription events?

You can use events for a small book, but it gets painful fast. The query above relies on two well-defined snapshots a month apart, which the snapshot table provides for free. Running off the event log forces you to reconstruct customer state on each anchor date with a window function or self-join, doubling the code and tripling the cost of every change. Build the daily snapshot once and run every MRR movement query against it.

What is the smallest data setup that still produces a defensible expansion number?

At minimum: a customer identifier stable across plan changes, a subscription state with mrr and status on two dates a month apart, and a clean separation between voluntary plan changes and pricing-rate changes. Without any one of those — if customer IDs rotate on upgrade, or pricing changes mix into the same field as plan choice — the metric lies silently and the only fix is to repair the upstream data.