Gross revenue retention in SQL

What gross revenue retention actually measures

Gross revenue retention, usually shortened to GRR, is the share of your starting recurring revenue that survives a window after you strip out every form of upside. No expansion, no upsell, no cross-sell, no price increase on renewal. Just the raw question: of every dollar of MRR you opened the period with, how many were still being paid by the same customers at the end. Cancellations cost you. Downgrades and seat drops cost you. Anything customers paid more does not help, because GRR caps at one hundred percent by construction.

This is the metric a SaaS finance team at Stripe, Snowflake, or Databricks pulls when the board asks how durable the existing book is. Net revenue retention, the more famous sibling, can hide a leaky bucket if expansion is large enough to mask churn and contraction. GRR refuses to flatter you. A healthy SaaS company runs GRR above ninety percent on annual contracts and above eighty-five on monthly. Below eighty and the conversation shifts from growth strategy to product-market-fit triage.

Picture the typical Wednesday ask from your VP of Finance: a board chart showing GRR for the last twelve months by acquisition cohort, with a three-month rolling average on top. You have a customers table with one row per active customer, a plan_changes table for downgrades, and cancellation timestamps. By Friday morning you need a query that returns clean numbers and a defence of every choice about what counts as churn and what counts as contraction. The rest of this post is that query, the traps on the way, and the language to use when a PM challenges the result.

GRR vs NRR

GRR and NRR look at the same population — customers you had at the start of the window — but they tell different stories. The difference comes down to whether expansion gets to push the number above one hundred percent.

Treat GRR as the clean signal of churn and downgrade quality. Treat NRR as the headline that captures the full retention engine including upsell. A great board package shows both. If GRR is healthy but NRR is flat, customer success works but expansion does not. If NRR is great but GRR is mediocre, you are masking attrition with seat growth — that lasts only as long as your customers keep hiring.

The SQL recipe

The simplest version uses two anchor dates and three numbers: starting MRR, MRR lost to churn during the window, and MRR lost to contraction during the window. The formula is (starting_mrr - churn_mrr - contraction_mrr) / starting_mrr, and the SQL just needs to compute those three numbers consistently for the same cohort and the same window.

WITH starting AS (
    SELECT SUM(monthly_mrr) AS start_mrr
    FROM customers
    WHERE active_on = '2026-04-01'
),
churn_mrr AS (
    SELECT SUM(monthly_mrr) AS churned_amount
    FROM customers
    WHERE active_on = '2026-04-01'
      AND churned_at BETWEEN '2026-04-01' AND '2026-05-01'
),
contraction AS (
    SELECT SUM(old_mrr - new_mrr) AS contraction_amount
    FROM plan_changes
    WHERE changed_at BETWEEN '2026-04-01' AND '2026-05-01'
      AND new_mrr < old_mrr
      AND new_mrr > 0
)
SELECT
    s.start_mrr,
    c.churned_amount,
    co.contraction_amount,
    (s.start_mrr - c.churned_amount - co.contraction_amount)::NUMERIC
        * 100 / NULLIF(s.start_mrr, 0) AS grr_pct
FROM starting s, churn_mrr c, contraction co;

A few details matter. The active_on filter pins the starting population so customers who joined during the window do not contaminate the denominator — GRR is about the cohort you opened with, not new business. The new_mrr > 0 clause on contraction defends against treating churn as contraction; a customer dropping to zero MRR cancelled, and that belongs in churn_mrr. The NULLIF around the denominator stops the query from blowing up on an empty start period during testing.

Run the query on a recent month, then sanity check the result against the simpler end_mrr / start_mrr for the same customers. If the two numbers disagree by more than a rounding error, one of your three buckets is leaking — usually contraction picking up customers who actually churned and were not flagged in customers.churned_at.

Cohort GRR

Period GRR — every active customer in a window — is what you brief to a board. Cohort GRR — a single acquisition month watched over time — tells you whether retention is actually improving or whether you are stacking bigger customers on a leaky base. Cohort GRR is the version PMs and growth teams trust most, because it isolates product changes from changes in mix.

WITH cohort_start AS (
    SELECT
        DATE_TRUNC('month', first_paid_at) AS cohort,
        user_id,
        monthly_mrr AS start_mrr
    FROM customers
    WHERE first_paid_at >= '2025-06-01'
      AND first_paid_at <  '2025-07-01'
),
cohort_now AS (
    SELECT
        cs.cohort,
        cs.user_id,
        cs.start_mrr,
        COALESCE(c.monthly_mrr, 0) AS current_mrr,
        CASE WHEN c.churned_at IS NOT NULL
             THEN cs.start_mrr
             ELSE 0
        END AS churned_mrr,
        CASE WHEN c.churned_at IS NULL
              AND COALESCE(c.monthly_mrr, 0) < cs.start_mrr
             THEN cs.start_mrr - c.monthly_mrr
             ELSE 0
        END AS contraction_mrr
    FROM cohort_start cs
    LEFT JOIN customers c USING (user_id)
)
SELECT
    cohort,
    SUM(start_mrr)        AS starting_mrr,
    SUM(churned_mrr)      AS churn_mrr,
    SUM(contraction_mrr)  AS contraction_mrr,
    (SUM(start_mrr) - SUM(churned_mrr) - SUM(contraction_mrr))
        * 100.0 / SUM(start_mrr) AS grr_pct
FROM cohort_now
GROUP BY cohort;

The query picks a single acquisition month — June 2025 here — and follows that exact set of customers forward to today. The LEFT JOIN is intentional because it keeps the cohort denominator stable. The CASE statements separate churn from contraction at the customer level: anyone with a churned_at timestamp counts the full starting MRR as churn, while everyone else contributes only the delta. Loop the same shape over twelve months of cohorts and you get a triangle chart finance teams love and product teams can act on.

Trend GRR with a rolling average

Once you can compute GRR for one month, the next ask is always the same: show it over time, and smooth out the noise. A rolling three-month average is the standard treatment. It dampens the lumpy months — large enterprise downgrades especially — and makes the underlying trend legible without hiding it.

WITH monthly AS (
    SELECT
        period_month,
        starting_mrr,
        churn_mrr,
        contraction_mrr,
        (starting_mrr - churn_mrr - contraction_mrr)
            * 100.0 / NULLIF(starting_mrr, 0) AS grr
    FROM mrr_snapshot
    WHERE period_month >= CURRENT_DATE - INTERVAL '12 months'
)
SELECT
    period_month,
    grr,
    AVG(grr) OVER (
        ORDER BY period_month
        ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
    ) AS grr_3m_avg
FROM monthly
ORDER BY period_month;

The window function smooths in a single pass: each month sees itself plus the two months before it. If a single account renegotiates a six-figure contract downward, the raw GRR will dip noticeably that month, but the three-month average will absorb the shock and show whether retention is genuinely trending or whether one bad month is the whole story. Plot both lines — the noisy actual and the smoothed average — and you give a finance audience the context they need to interpret a single data point.

Common pitfalls

The first pitfall is producing a GRR above one hundred percent. That is mathematically impossible because GRR has no expansion in the numerator. If your query returns 102%, you almost certainly added expansion to the formula by accident — usually through a plan_changes filter that picked up upgrades when it was supposed to pick up downgrades. Re-check the sign on every CTE before publishing the chart.

The second pitfall is window inconsistency. Some teams report monthly GRR and then annualise by multiplying by twelve, which is wrong because GRR compounds. Monthly GRR of 0.99 corresponds to roughly 88.6% annualised, not 88%. Pick one — monthly or annual — and stick to it in every chart and every meeting. Inconsistency between two slides is the single fastest way to lose credibility with a finance audience.

The third pitfall is conflating cohort GRR and period GRR. Cohort GRR follows a fixed group of customers forward in time. Period GRR slices the entire base at a particular month. These produce different numbers and answer different questions. Label your charts so the audience knows which one they are looking at.

The fourth pitfall is fuzzy contraction definitions. Plan tier downgrades, seat reductions, discount renewals, and usage-tier drops all reduce MRR, but each one tells a different story. A team that lumps all four into one bucket will never know whether sales is over-discounting at renewal or whether customers are genuinely using less. Write a definition document and reference it on every contraction chart.

The fifth pitfall is treating subscription pauses as churn. A monthly plan that pauses for one billing cycle and resumes is not churn. If you count the pause month as a cancellation, your GRR will look artificially low and your churn investigation will chase ghosts. A pause is a separate status, and only pauses that turn into permanent cancellations roll up into churn for the GRR calculation.

Optimization tips

For most SaaS shops the GRR query runs over a few million rows and finishes in seconds, so optimisation is rarely the bottleneck. When the query drags, it is almost always because the analyst is computing GRR from raw event logs instead of a snapshot table. Materialise a monthly snapshot of subscription state and query against that.

Partition the customers and plan_changes tables by month. On Snowflake and BigQuery, confirm partition pruning is actually engaging by reading the query profile — if a one-month query scans more than two months of partitions, the planner is not using the pruner. On Postgres, declarative partitioning on state_date plus a compound index on (state_date, customer_id) keeps the planner honest.

Pre-aggregate the three components — starting MRR, churn MRR, and contraction MRR — into a grr_components_monthly table. The trend query then becomes a single scan of a tiny table with twelve rows per year, and the rolling-average window runs in milliseconds. Rebuild the aggregate once a day in a scheduled job; an hourly refresh is overkill for most teams.

Related reading

• How to calculate churn in SQL
• How to calculate contraction MRR in SQL
• How to calculate expansion MRR in SQL
• How to calculate cohort retention in SQL
• SQL window functions interview questions

If you want to drill SaaS retention questions like this every day, NAILDD is launching with 500+ SQL problems across exactly this pattern.

FAQ

What is a healthy GRR?

For annual contracts on enterprise plans, anything above ninety percent is healthy and the top quartile sits above ninety-five. For monthly self-serve SaaS the band is wider because churn naturally runs higher — eighty to ninety percent is normal, and consistently above ninety is excellent. The number that matters more than the absolute level is the trend: GRR moving upward over four to six quarters tells a clearer growth story than a single high reading.

Is GRR or NRR the more important metric?

Both, for different audiences. Investors and the board usually focus on NRR because it captures expansion, which is the engine of efficient growth. Customer success and product usually focus on GRR because it isolates the durability of what already exists from the upside that sales and marketing keep adding. A mature reporting cadence shows both side by side and watches the gap: if NRR is far above GRR, expansion is doing all the work and the underlying retention may be soft.

My GRR is dropping — how do I diagnose?

Decompose it into the two inputs. Pull churn MRR and contraction MRR separately for the last six months and plot them on the same axis. If churn is rising, the conversation moves to onboarding and product-market fit, and the customers you are losing should be interviewed and segmented by what changed at cancellation. If contraction is rising, the conversation moves to pricing, packaging, and account management; renewals are coming through but at lower prices. The two failure modes call for different playbooks.

How do I annualise monthly GRR?

Multiply twelve consecutive monthly retention rates rather than multiplying a single rate by twelve. If your monthly GRR averages 0.99 over the last twelve months, annual GRR is roughly 0.99 raised to the twelfth power, which is about 88.6%. Alternatively, apply (start - churn - contraction) / start directly across a twelve-month window. The two approaches should agree within a point or two; if they diverge, the snapshot data has gaps.

How are pauses and freezes handled?

The cleanest convention is: any pause longer than the median return-to-active window for your business counts as churn, and anything shorter contributes zero to GRR for the pause months and rejoins the active base on resumption. Document the threshold, because finance will notice the inconsistency if paused customers are in one quarter and out the next.

Does seat growth in an existing account affect GRR?

No, and that is the entire point of the metric. If an account opened the period at 100 seats and grew to 130, the extra 30 seats are expansion MRR and live in NRR, not GRR. GRR for that account is exactly 100% as long as no seats were dropped. Leaving expansion out of GRR is what makes the metric trustworthy as a measure of retention quality — it cannot be flattered by good sales quarters, only by keeping the customers you already have.