How to calculate ARR in SQL

What ARR is and why it sits at the top of every SaaS deck

ARR — Annual Recurring Revenue — is the projected value of the next twelve months of subscription revenue, assuming the current book of business stays exactly where it is. It is the number every SaaS board meeting opens with, the number Snowflake, Databricks, and Linear lean on when they brief investors, and the number a Stripe analyst is expected to pull from the warehouse before standup.

What makes ARR deceptively hard is that the headline formula — MRR times twelve — rarely fails on its own. The pain lives one level down: prepaid annual contracts, biennial deals, mid-month upgrades, refunds, paused accounts, and one-time onboarding fees that finance booked as revenue but have no business inside a recurring metric. Each decision moves the number, and each one is a question an interviewer will probe.

This post walks through the SQL you need: the textbook current-ARR query, the mixed-billing-period version, the daily ARR curve, the new-versus-churn waterfall, and the net-of-discount variant. Every query is Postgres and translates with minor edits to Snowflake, BigQuery, or Redshift.

The formula and what it actually means

The textbook definition is the one you write first.

ARR = MRR × 12

That works when every subscription is monthly. The moment a customer signs an annual or multi-year contract, MRR is no longer readable directly off the order table — you have to normalize.

ARR = SUM(annual_contracts.amount)
    + SUM(monthly_contracts.amount × 12)
    + SUM(biennial_contracts.amount / 2)

Take every active subscription, annualize whatever it bills, and sum. A monthly customer paying 100 USD adds 1,200 USD. A 1,200 USD annual contract also adds 1,200 USD. A 2,000 USD biennial contract adds 1,000 USD.

The data model we will use

Assume two tables.

subscriptions       (customer_id, mrr, amount, billing_period,
                     status, started_at, cancelled_at)
subscription_events (customer_id, event_at, mrr_change, kind)

subscriptions.status is one of active, past_due, paused, cancelled, expired. billing_period is monthly, annual, biennial, or triennial. amount is what the customer is billed per period in USD; mrr is the same number as a monthly equivalent. subscription_events is the append-only log of upgrades, downgrades, cancellations, and reactivations, with mrr_change signed — a churn is the customer's full MRR with a minus sign.

Six SQL queries you will actually ship

1. Textbook current ARR

If every subscription is monthly, the whole calculation collapses to a single aggregate.

SELECT SUM(mrr) * 12 AS arr
FROM subscriptions
WHERE status IN ('active', 'past_due');

past_due is in the filter because customers in delinquency are still under contract — the card simply did not clear this month. A customer becomes a true churn the moment status flips to cancelled or expired.

2. Mixed billing periods — the version interviewers want

The second the book has any annual or multi-year contracts, the simple sum lies. You have to normalize per row.

SELECT
    SUM(
        CASE
            WHEN billing_period = 'monthly'   THEN amount * 12
            WHEN billing_period = 'annual'    THEN amount
            WHEN billing_period = 'biennial'  THEN amount / 2.0
            WHEN billing_period = 'triennial' THEN amount / 3.0
            ELSE 0
        END
    )::NUMERIC AS arr
FROM subscriptions
WHERE status IN ('active', 'past_due');

The explicit 2.0 and 3.0 force floating-point division — drop the decimal point and Postgres silently does integer division on an integer amount, which is the most common bug in this query. The ELSE 0 is defensive: a future billing period nobody told analytics about contributes zero rather than crashing the warehouse with a null.

3. The daily ARR curve

To see how ARR has moved over time, stop reading the subscriptions table and start reading the events log. Generate a daily spine, join the daily net change, and take a running sum.

WITH days AS (
    SELECT generate_series('2026-01-01'::DATE,
                           CURRENT_DATE,
                           INTERVAL '1 day')::DATE AS day
),
daily AS (
    SELECT
        event_at::DATE              AS day,
        SUM(mrr_change)             AS net_mrr_change
    FROM subscription_events
    GROUP BY 1
)
SELECT
    d.day,
    SUM(COALESCE(daily.net_mrr_change, 0))
        OVER (ORDER BY d.day) * 12  AS arr
FROM days d
LEFT JOIN daily USING (day)
ORDER BY d.day;

The window function SUM(...) OVER (ORDER BY day) is the part that hires people. Without it, you get the daily change in ARR, not the level, and the chart looks like noise around zero. If you have not stared at LAG, LEAD, and running aggregates recently, the SQL window functions interview questions post drills exactly this pattern.

4. New ARR versus churned ARR

Splitting positive and negative MRR changes is the standard SaaS waterfall.

SELECT
    DATE_TRUNC('month', event_at)::DATE                       AS month,
    SUM(CASE WHEN mrr_change > 0 THEN mrr_change END) * 12    AS new_arr,
    SUM(CASE WHEN mrr_change < 0 THEN mrr_change END) * 12    AS churned_arr,
    SUM(mrr_change) * 12                                      AS net_new_arr
FROM subscription_events
WHERE event_at >= '2026-01-01'
GROUP BY 1
ORDER BY 1;

This is the query that becomes a stacked bar on every SaaS investor update. The interesting moment is when net new ARR goes negative — gross numbers can still look healthy while the company is shrinking. The companion churn analysis post covers the rate-based view of the same dynamic.

5. ARR by plan or segment

Pricing tiers, customer segments, geographies, and acquisition channels are all natural cuts.

SELECT
    plan,
    COUNT(*)                                            AS active_subs,
    SUM(
        CASE
            WHEN billing_period = 'monthly' THEN amount * 12
            WHEN billing_period = 'annual'  THEN amount
            ELSE amount * 12.0 / period_months
        END
    )::NUMERIC                                          AS arr
FROM subscriptions
WHERE status IN ('active', 'past_due')
GROUP BY plan
ORDER BY arr DESC;

amount * 12.0 / period_months is the generalized annualization, handling future billing periods without a CASE rewrite. A concentration cut is where the conversation pivots from "is ARR growing" to "is ARR healthy" — a book where the top three accounts represent half of ARR carries very different risk than a long-tail book of the same size.

6. Net ARR after discounts

If pricing uses promotional discounts, gross ARR overstates run-rate revenue. Multiply each row by (1 - discount_pct) before summing.

SELECT
    SUM(
        CASE
            WHEN billing_period = 'monthly' THEN amount * 12
            WHEN billing_period = 'annual'  THEN amount
            ELSE amount * 12.0 / period_months
        END
        * (1 - COALESCE(discount_pct, 0))
    )::NUMERIC AS net_arr
FROM subscriptions
WHERE status IN ('active', 'past_due');

Finance almost always wants this version. The two numbers diverge dramatically when a company is buying growth with first-year discounts.

What belongs in ARR and what does not

ARR includes only revenue components that recur on a predictable cadence. Subscription fees count. Subscribed add-ons count — a customer paying 1,000 USD per month plus 200 USD per month for premium support contributes 14,400 USD of ARR, not 12,000 USD. Per-seat licensing counts the same way.

What does not count is anything one-off. Setup fees, implementation, professional services, single-purchase add-ons, annual loyalty bonuses billed once — all belong on the income statement but not inside ARR. The interview classic: "the customer paid 1,200 USD this year — 1,000 USD subscription and 200 USD setup; what is their ARR contribution?" The answer is 12,000 USD; the setup fee is omitted because nobody expects it next year.

Usage-based components are the genuinely hard case. Pure pay-as-you-go is not recurring and is typically excluded or tracked as a separate "usage revenue" metric. Committed-usage contracts, where the customer pre-commits to a consumption floor, are usually included at the committed amount because the floor is contractual.

Common pitfalls

The first trap is including one-time revenue. Setup fees, implementation, training, and professional services hit the same revenue ledger as subscriptions, and a naive SUM(revenue) silently capitalizes them into ARR. The fix is to compute ARR from the subscription table, not the revenue ledger, and reconcile the two only at the income-statement level.

The second trap is forgetting churned customers. A subscription cancelled three days ago is no longer recurring — but if the query reads from a snapshot taken last week, the row is still active. Anchor the filter on status IN ('active', 'past_due') at the moment of calculation and validate that the events log has caught up. A reconciliation query that computes ARR two ways — from the snapshot and as a running sum of events — should agree to the dollar; if it does not, your events log has gaps.

The third trap is reading MRR deltas without smoothing. One enterprise customer signing a 250,000 USD annual contract on a Tuesday moves ARR by a quarter of a million dollars in a day, and the daily chart looks like a step function rather than a trend. For executive reporting the right view is rolling — a 28-day or 90-day moving average.

The fourth trap is confusing ARR with run-rate revenue. ARR is the projection of the current subscription book over twelve months. Run-rate is whatever happened most recently times twelve. Run-rate includes one-time fees and usage; ARR does not. A growth-mode company often shows run-rate above ARR because new business closes faster than the book annualizes.

The fifth trap is integer division in the multi-year case. amount / 2 for biennial contracts works only when amount is numeric or floating-point. If the warehouse models money in cents as an integer, the division truncates and ARR comes out short by every odd-cent contract. Always force it: amount / 2.0, amount::NUMERIC / 2, or an explicit cast.

Optimization tips

Most ARR queries are cheap because the active subscription set is small relative to the events log. Even a million active rows aggregates in milliseconds. The real optimization story is the events log, which grows with every upgrade, downgrade, and reactivation.

The first lever is partitioning the events table by event_at — monthly partitions are conventional. Every ARR-curve query is naturally bounded by a date range, and partition pruning collapses a hundred-month scan into the two months you need. On Snowflake, cluster on event_at; on BigQuery, partition plus cluster on customer_id.

The second lever is materializing the daily aggregate. The ARR curve query is the canonical materialized-view candidate: read by every dashboard, inputs change only when new events land, and recomputing the spine on every load is wasteful. A nightly refresh of a daily_arr table keeps the chart loading in under a second.

Related reading

• How to calculate ARPU in SQL — the per-user counterpart to the company-level ARR view.
• How to calculate churn in SQL — the negative side of the ARR waterfall and the metric that quietly caps growth.
• Annual vs monthly subscription mix in SQL — directly relevant when the billing-period split inside ARR starts to matter.
• SQL window functions interview questions — the running-sum pattern from the ARR curve, plus a dozen more.

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

FAQ

ARR or MRR — which one should I report?

Use both, and reach for each in the right room. MRR is the operational metric: it moves on a monthly cadence, growth teams target it, and onboarding experiments measure against it. ARR is the strategic metric: board decks open with it, valuation conversations anchor on it, and annual planning runs against it. The numbers are mechanically the same — one is twelve times the other — but the audiences are different, and reporting only one usually means missing the conversation the other one drives.

What counts as an "active" subscription?

The convention is status IN ('active', 'past_due'). Active is unambiguous. Past-due means the renewal payment failed but the customer has not formally cancelled — they are in a grace window where the company is retrying the card and sending dunning emails. Excluding past-due understates ARR by the size of your dunning population, which is non-trivial in consumer products. cancelled, expired, and paused are all excluded by industry consensus.

How do I handle annual contracts that were prepaid?

Annualize them and divide by their period length in years. A customer who paid 1,200 USD up front for a one-year contract contributes 1,200 USD of ARR. A 2,000 USD prepayment on a two-year contract contributes 1,000 USD — the contract amount divided by the length in years. The cash hit the bank in one transaction; ARR is a steady-state projection and does not care.

Is ARR the same as bookings or revenue?

No. Bookings is the total contracted value at the moment a deal is signed: a three-year contract worth 3,000 USD per year books at 9,000 USD. Revenue is what hits the income statement under the company's recognition rules, usually spread across the service period. ARR is the projection of the current subscription book over the next twelve months. A healthy SaaS quarter often shows bookings well above ARR and revenue below ARR. An analyst who can keep the three straight is a senior analyst.

Should ARR include usage-based revenue?

Only the committed floor. Pure pay-as-you-go is not recurring contractually and is usually reported as a separate "usage revenue" line. If a customer signs a contract committing to 50,000 USD per year of consumption, that 50,000 USD belongs in ARR because the floor is contractual; anything above is upside that belongs in usage revenue. Blending the two into one headline number invariably gets questioned.