How to calculate ARR Growth in SQL

What ARR growth is and why investors live by it

Annual Recurring Revenue growth is the single number that frames every board meeting at a subscription business. Snowflake, Databricks, Stripe, Notion and every other SaaS company lead investor updates with ARR and ARR growth year over year. The first tells the market how big the business is; the second tells the market how fast it is becoming bigger — and in software, the second is what gets the multiple. A company at 60 percent YoY growth trades at a fundamentally different valuation than the same company at 20 percent.

The trap is that ARR growth looks like a one-line formula and almost nobody computes it correctly the first try. The headline hides four moving parts — new, expansion, churn, contraction — and any of them can carry the quarter while the others quietly drag. I have watched a sales VP celebrate a 35 percent YoY print without realizing gross new ARR was down and the number was propped up by a one-off expansion deal that would not repeat. That is what the decomposition is for.

This post walks through the SQL: snapshot, QoQ and YoY growth, the four-way decomposition, Net New ARR, and a combined view. Every query runs in Postgres with minor edits for Snowflake, BigQuery, or Redshift.

Formulas: ARR, ARR growth, and the four components

ARR is annualized monthly recurring revenue. At any snapshot date, take the MRR of every active subscription and multiply by twelve. ARR growth is the period-over-period change in that snapshot, expressed as a percentage.

ARR_end    = ARR_start + New ARR + Expansion ARR - Churn ARR - Contraction ARR
ARR Growth = (ARR_end - ARR_start) / ARR_start

The four components on the right are the diagnostic engine. New ARR comes from brand-new customers. Expansion ARR comes from existing customers who upgrade — seats, plan tier, add-ons. Churn ARR is lost when a customer cancels entirely. Contraction ARR is lost when a customer downgrades but stays.

Net New ARR = (New ARR + Expansion ARR) - (Churn ARR + Contraction ARR)

Net New ARR is the metric sales and customer success live on. ARR growth is what the CFO and investors look at; Net New ARR is what tells the operating team whether they are winning. When Net New ARR is positive and accelerating the business compounds. When it goes flat or negative the headline percentage catches up one or two quarters later.

The data model we will use

For every example below, assume two tables any reasonable billing stack — Stripe, Chargebee, or an in-house service at a company like Vercel or DoorDash — exposes in some form.

subscriptions  (subscription_id, customer_id, mrr, status, started_at, ended_at)
mrr_movements  (customer_id, type, amount, event_date)

subscriptions holds the current state of every contract; status takes values like active or churned and mrr is in USD. mrr_movements is the event log of MRR changes — each row is a single movement classified as new, expansion, churn, or contraction, with amount as the MRR delta (always positive; the sign is implied by type). Most billing systems produce this log directly; if yours does not, derive it from subscription history with LAG and persist the result.

Five SQL queries you will actually ship

1. ARR snapshot by quarter

Sum MRR across active subscriptions, multiply by twelve, group by quarter.

SELECT
    DATE_TRUNC('quarter', started_at)::DATE    AS quarter,
    SUM(mrr) * 12                              AS arr
FROM subscriptions
WHERE status = 'active'
GROUP BY 1
ORDER BY 1;

In production, replace the date filter with a snapshot model that captures the active set on the last day of each quarter — the structure is identical. The output is one row per quarter with the headline ARR you would put in an investor update.

2. ARR growth QoQ and YoY

Window functions earn their keep. LAG(arr, 1) pulls the previous quarter into the same row; LAG(arr, 4) pulls the same quarter from the prior year. Both percentages drop out of one query.

WITH arr_snapshots AS (
    SELECT
        DATE_TRUNC('quarter', event_date)::DATE    AS quarter,
        SUM(mrr) * 12                              AS arr
    FROM subscriptions
    WHERE status = 'active'
    GROUP BY 1
)
SELECT
    quarter,
    arr,
    ROUND(100.0 * (arr - LAG(arr)    OVER (ORDER BY quarter))
                / NULLIF(LAG(arr)    OVER (ORDER BY quarter), 0), 2)    AS qoq_pct,
    ROUND(100.0 * (arr - LAG(arr, 4) OVER (ORDER BY quarter))
                / NULLIF(LAG(arr, 4) OVER (ORDER BY quarter), 0), 2)    AS yoy_pct
FROM arr_snapshots
ORDER BY quarter;

NULLIF(..., 0) guards the divide-by-zero case in the earliest periods. The result is one row per quarter with QoQ and YoY growth side by side — exactly what gets pasted into a board deck.

3. The four-way decomposition

This is the query that turns ARR growth from a headline into a diagnosis. Sum each movement type, annualize, put the four components on one row.

SELECT
    DATE_TRUNC('quarter', event_date)::DATE                              AS quarter,
    SUM(CASE WHEN type = 'new'         THEN amount END) * 12             AS new_arr,
    SUM(CASE WHEN type = 'expansion'   THEN amount END) * 12             AS expansion_arr,
    SUM(CASE WHEN type = 'churn'       THEN amount END) * 12             AS churn_arr,
    SUM(CASE WHEN type = 'contraction' THEN amount END) * 12             AS contraction_arr,
    (
        SUM(CASE WHEN type = 'new'         THEN amount END)
      + SUM(CASE WHEN type = 'expansion'   THEN amount END)
      - SUM(CASE WHEN type = 'churn'       THEN amount END)
      - SUM(CASE WHEN type = 'contraction' THEN amount END)
    ) * 12                                                                AS net_new_arr
FROM mrr_movements
GROUP BY 1
ORDER BY 1;

Reading left to right tells the story of the quarter. New and expansion flat with churn doubled is a retention problem dressed as a growth slowdown. Expansion hot but new dropping points to a pipeline issue masked by a healthy installed base. The headline from query 2 never tells you which — this query always does.

4. Net New ARR for the sales view

Sales and customer success care about Net New ARR more than ARR growth, because it is the metric they can move quarter to quarter. The query collapses the four components into added vs lost.

SELECT
    DATE_TRUNC('quarter', event_date)::DATE                              AS quarter,
    SUM(CASE WHEN type IN ('new', 'expansion')      THEN amount END) * 12 AS added,
    SUM(CASE WHEN type IN ('churn', 'contraction')  THEN amount END) * 12 AS lost,
    (
        SUM(CASE WHEN type IN ('new', 'expansion')     THEN amount END)
      - SUM(CASE WHEN type IN ('churn', 'contraction') THEN amount END)
    ) * 12                                                                AS net_new_arr
FROM mrr_movements
GROUP BY 1
ORDER BY 1;

A negative Net New ARR quarter is a four-alarm fire — the installed base is shrinking faster than new business can refill it. It happens to most SaaS companies at least once, and it is the kind of number a CRO has to explain on the next earnings call.

5. ARR growth combined with the decomposition

Sometimes you want the headline percentage and components on the same row. Join the snapshot view to the movement view by quarter.

WITH arr_snapshots AS (
    SELECT
        DATE_TRUNC('quarter', started_at)::DATE    AS quarter,
        SUM(mrr) * 12                              AS arr_end
    FROM subscriptions
    WHERE status = 'active'
    GROUP BY 1
),
arr_components AS (
    SELECT
        DATE_TRUNC('quarter', event_date)::DATE    AS quarter,
        SUM(CASE WHEN type = 'new'         THEN amount END) * 12     AS new_arr,
        SUM(CASE WHEN type = 'expansion'   THEN amount END) * 12     AS expansion_arr,
        SUM(CASE WHEN type = 'churn'       THEN amount END) * 12     AS churn_arr,
        SUM(CASE WHEN type = 'contraction' THEN amount END) * 12     AS contraction_arr
    FROM mrr_movements
    GROUP BY 1
)
SELECT
    s.quarter,
    s.arr_end,
    ROUND(100.0 * (s.arr_end - LAG(s.arr_end, 4) OVER (ORDER BY s.quarter))
                / NULLIF(LAG(s.arr_end, 4) OVER (ORDER BY s.quarter), 0), 2)
                                                                          AS yoy_growth_pct,
    c.new_arr, c.expansion_arr, c.churn_arr, c.contraction_arr
FROM arr_snapshots s
LEFT JOIN arr_components c USING (quarter)
ORDER BY s.quarter;

This is the query I would pin to a SaaS metrics dashboard. Headline growth on the left, decomposition on the right, no flipping between tabs to figure out whether a slowdown is a churn problem or a pipeline problem.

Common pitfalls

The first trap is confusing ARR with bookings. ARR is annualized current MRR; bookings is total contracted value. A three-year deal at 10,000 USD per month is 360,000 USD of bookings but only 120,000 USD of ARR. Reporting bookings as ARR will quietly inflate the headline by two or three times on multi-year contracts. Always annualize from MRR at the snapshot date, never from contract value.

The second trap is mishandling multi-currency revenue. If your business sells in USD, EUR, GBP and a tail of local currencies, the snapshot ARR depends on which FX rates you use and when you locked them. Most finance teams freeze the rate at the start of the fiscal year so ARR movements reflect business performance rather than FX swings. Pick one convention and apply it consistently — otherwise half your growth number is currency noise.

The third trap is discount handling. A 100 USD per month subscription with a permanent 20 USD discount contributes 960 USD of ARR, not 1,200. Promotional credits and free months are trickier — discounted price vs full price changes the metric materially. The standard convention is net ARR after permanent discounts and gross ARR before promotional credits; the important thing is to pick a definition and stick with it.

The fourth trap is including non-recurring revenue. Setup fees, professional services, and overage billing are all real revenue but none of them are recurring. Adding them to the snapshot inflates the number without adding predictability — the property ARR is supposed to measure. Tag each line item as recurring or non-recurring and only feed the recurring portion into the annualization.

The fifth trap is snapshot timing. ARR at quarter end is different from ARR averaged across the quarter. A quarter ending Friday with two large deals closing the Monday after looks smaller than a quarter ending Monday. Whatever you pick, write it into the metric definition and never silently change it.

Optimization tips

Most ARR queries do not need exotic tuning, but once mrr_movements crosses tens of millions of rows, two things matter. First, partition by month. Every ARR query above is naturally bounded by date, so partition pruning collapses the scan. Snowflake handles this through clustering keys; BigQuery and Postgres expose explicit partitioning.

Second, materialize the per-quarter aggregates. Query 5 is the perfect candidate for a nightly materialized view. The decomposition is read constantly by finance and the exec team, and recomputing it on every dashboard load wastes compute on Snowflake or Databricks. dbt incremental models push the aggregation upstream and the BI layer becomes effectively free.

Related reading

• SQL window functions interview questions — the LAG pattern, plus a dozen more.
• How to calculate churn in SQL — the churn component of ARR growth.
• How to calculate ARPU in SQL — the per-user revenue view that complements ARR.
• Annual vs monthly subscription mix in SQL — how plan mix shapes the ARR snapshot.

If you want to drill SQL like this every day, NAILDD is launching with hundreds of analytics SQL problems built around this pattern — revenue, cohorts, decomposition, window functions.

FAQ

What is a healthy ARR growth rate?

It depends on stage. Pre-product-market-fit companies often post triple-digit YoY growth because the base is small. Series A SaaS typically runs 100 to 300 percent YoY when things go well. By Series B and C the bar moves to 50 to 100 percent. Public SaaS at scale — Snowflake, Databricks, Salesforce — runs at 20 to 50 percent and the market rewards consistency. A single number out of context tells you little; the trajectory and decomposition are what investors actually grade.

ARR growth is slowing — is that bad?

Not necessarily. Percentage growth gets mechanically harder as the base grows — a company going from 10 to 20 million ARR doubled, but the same 10 million of Net New ARR added to a 200 million base reads as 5 percent growth. Look at absolute Net New ARR alongside the percentage. If Net New ARR is flat or growing while the percentage shrinks, the business is healthy and maturing. If both shrink, that is a real problem.

What is the difference between Net New ARR and Gross New ARR?

Gross New ARR counts only positive movements — new and expansion. Net New ARR subtracts churn and contraction, giving the actual change in installed base. Sales teams sometimes prefer Gross because it isolates pipeline work from retention dynamics they cannot fully control. Finance and investors always look at Net because that ties out to the change in the ARR snapshot.

Can Net New ARR be negative?

Yes, and it is one of the worst signals a SaaS business can produce. A negative quarter means churn and contraction together exceeded new and expansion — the installed base shrank in absolute terms. One quarter is recoverable. Two in a row triggers a serious internal review. Three becomes a story the CEO has to tell investors.

Should I report ARR growth quarterly or annually?

Both, for different audiences. Annual (YoY) is what investors live by — it strips out seasonality and is what gets quoted in earnings releases. Quarterly (QoQ) is the operating metric — it surfaces inflection points six to nine months before they show up in the annual number. Most finance teams put YoY on the headline slide of the board deck and keep QoQ for internal reviews.