How to Calculate Net Revenue Retention in SQL

Net Revenue Retention tells you one thing: for the customers you had at the start of a period, how much are they paying now? An NRR above 100% means your existing customer base is growing on its own, even without new logos. Below 100% and you're on a treadmill — new sales just replace what you're losing.

Investors obsess over this number because it predicts compounding. A company with 120% NRR doubles its revenue from existing customers roughly every four years, before a single new deal closes. A company with 90% NRR halves it.

The formula is straightforward. The SQL is not, mostly because of denomination choices and time-window semantics that nobody agrees on. Here's how to get it right.

The formula

NRR = (Starting MRR + Expansion - Contraction - Churn) / Starting MRR * 100

Starting MRR is what your existing cohort was paying at the beginning of the period. Expansion is any increase from those same customers (upgrades, seat additions, usage growth). Contraction is decreases (downgrades, seat removals). Churn is revenue from customers who canceled entirely. New customers acquired during the period are excluded — that's the whole point.

The query below assumes a customer_mrr table with one row per customer per month. If you're computing MRR from raw Stripe subscriptions, see How to Calculate Real MRR from the Stripe API for the normalization logic, or build the MRR waterfall from How to Build a Revenue Waterfall Chart in SQL.

Month-over-month NRR

-- customer_mrr(customer_id, month, mrr)
-- One row per customer per month where mrr > 0
 
WITH mrr_changes AS (
  SELECT
    curr.month,
    prev.mrr                                         AS starting_mrr,
    CASE WHEN curr.mrr > prev.mrr
         THEN curr.mrr - prev.mrr ELSE 0 END        AS expansion,
    CASE WHEN curr.mrr < prev.mrr AND curr.mrr > 0
         THEN prev.mrr - curr.mrr ELSE 0 END        AS contraction,
    CASE WHEN curr.mrr IS NULL OR curr.mrr = 0
         THEN prev.mrr ELSE 0 END                   AS churn
  FROM customer_mrr prev
  LEFT JOIN customer_mrr curr
    ON  prev.customer_id = curr.customer_id
    AND curr.month = prev.month + INTERVAL '1 month'
  WHERE prev.mrr > 0
)
 
SELECT
  month,
  SUM(starting_mrr)                                  AS starting_mrr,
  SUM(expansion)                                     AS expansion,
  SUM(contraction)                                   AS contraction,
  SUM(churn)                                         AS churn,
  ROUND(
    (SUM(starting_mrr) + SUM(expansion)
     - SUM(contraction) - SUM(churn))
    / NULLIF(SUM(starting_mrr), 0) * 100,
    1
  )                                                  AS nrr_pct
FROM mrr_changes
GROUP BY month
ORDER BY month;

The LEFT JOIN is critical. When a customer has a row in the previous month but no row in the current month, that's churn. An INNER JOIN silently drops churned customers and inflates your NRR — I've seen this bug in production dashboards that went unquestioned for months because "110% NRR sounds right for us."

One subtlety: this query uses the month after the starting month as the label. So the row for 2026-07 tells you what happened to June's cohort when July arrived.

Trailing 12-month NRR

Month-over-month NRR is noisy. One large customer churning in March sends it to 85%; a big expansion in April pushes it to 115%. Investors and board decks want trailing 12-month NRR, which smooths the signal.

The concept: take the customers who were paying you 12 months ago. What are they collectively paying now?

WITH cohort AS (
  -- Customers active 12 months ago and their MRR at that time
  SELECT
    customer_id,
    mrr AS mrr_12m_ago
  FROM customer_mrr
  WHERE month = DATE_TRUNC('month', CURRENT_DATE) - INTERVAL '12 months'
    AND mrr > 0
),
current_state AS (
  -- What those same customers pay now (0 if churned)
  SELECT
    c.customer_id,
    c.mrr_12m_ago,
    COALESCE(m.mrr, 0) AS mrr_now
  FROM cohort c
  LEFT JOIN customer_mrr m
    ON  c.customer_id = m.customer_id
    AND m.month = DATE_TRUNC('month', CURRENT_DATE) - INTERVAL '1 month'
)
 
SELECT
  SUM(mrr_12m_ago)                                   AS starting_mrr,
  SUM(mrr_now)                                       AS ending_mrr,
  SUM(CASE WHEN mrr_now > mrr_12m_ago
       THEN mrr_now - mrr_12m_ago ELSE 0 END)        AS expansion,
  SUM(CASE WHEN mrr_now < mrr_12m_ago AND mrr_now > 0
       THEN mrr_12m_ago - mrr_now ELSE 0 END)        AS contraction,
  SUM(CASE WHEN mrr_now = 0
       THEN mrr_12m_ago ELSE 0 END)                  AS churn,
  ROUND(SUM(mrr_now)::NUMERIC
        / NULLIF(SUM(mrr_12m_ago), 0) * 100, 1)      AS nrr_12m_pct
FROM current_state;

The denominator trap lives here. Some teams use the average MRR across all 12 months instead of the single starting snapshot. Others use the MRR from 12 months ago but include customers who joined during the window. Both inflate the denominator and depress NRR. The cleanest approach: fix the cohort to exactly the customers active at the start, and use exactly their starting MRR. That's what this query does.

If you're under $5M ARR with a small customer base, a single enterprise churn can swing your trailing 12-month NRR by 20 points. At that scale, monthly NRR with a 3-month rolling average is more useful than pretending you have enough data for a stable annual metric.

Cohort-based NRR

The trailing 12-month number tells you the aggregate story, but it hides trends. Are the customers you signed last year retaining better or worse than the ones from two years ago? Cohort-based NRR answers that.

For each signup cohort, measure what they're paying at 3, 6, 12, and 18 months relative to their first full month of revenue.

WITH first_month AS (
  SELECT
    customer_id,
    MIN(month) AS cohort_month,
    MIN(mrr)   AS initial_mrr
  FROM customer_mrr
  WHERE mrr > 0
  GROUP BY customer_id
),
cohort_mrr AS (
  SELECT
    f.cohort_month,
    -- Months since first payment
    EXTRACT(YEAR FROM AGE(m.month, f.cohort_month)) * 12
      + EXTRACT(MONTH FROM AGE(m.month, f.cohort_month)) AS months_since,
    f.initial_mrr,
    COALESCE(m.mrr, 0) AS current_mrr
  FROM first_month f
  LEFT JOIN customer_mrr m
    ON f.customer_id = m.customer_id
)
 
SELECT
  cohort_month,
  COUNT(DISTINCT CASE WHEN months_since = 0
        THEN cohort_month END)                        AS cohort_size,
  SUM(CASE WHEN months_since = 0
      THEN initial_mrr END)                           AS month_0_mrr,
  ROUND(SUM(CASE WHEN months_since = 3
      THEN current_mrr ELSE 0 END)::NUMERIC
    / NULLIF(SUM(CASE WHEN months_since = 0
      THEN initial_mrr END), 0) * 100, 1)            AS nrr_3m,
  ROUND(SUM(CASE WHEN months_since = 6
      THEN current_mrr ELSE 0 END)::NUMERIC
    / NULLIF(SUM(CASE WHEN months_since = 0
      THEN initial_mrr END), 0) * 100, 1)            AS nrr_6m,
  ROUND(SUM(CASE WHEN months_since = 12
      THEN current_mrr ELSE 0 END)::NUMERIC
    / NULLIF(SUM(CASE WHEN months_since = 0
      THEN initial_mrr END), 0) * 100, 1)            AS nrr_12m,
  ROUND(SUM(CASE WHEN months_since = 18
      THEN current_mrr ELSE 0 END)::NUMERIC
    / NULLIF(SUM(CASE WHEN months_since = 0
      THEN initial_mrr END), 0) * 100, 1)            AS nrr_18m
FROM cohort_mrr
GROUP BY cohort_month
HAVING SUM(CASE WHEN months_since = 0 THEN initial_mrr END) > 0
ORDER BY cohort_month;

This is the most actionable view of retention you can build. If your Q1 2025 cohort shows 95% NRR at 12 months but your Q3 2025 cohort shows 85% at 6 months, something changed — pricing, onboarding, customer mix, product quality — and you should investigate before the 12-month number confirms the damage.

It also integrates directly with cohort retention analysis. Logo retention (did they stay?) and revenue retention (how much do they pay?) tell different stories. A customer who downgrades from $500/mo to $50/mo is retained in logo terms but essentially churned in revenue terms.

Gross Revenue Retention: the number NRR hides

Here's the problem with NRR: a company with 40% annual churn and massive expansion can still report 110% NRR. Sounds great until you realize almost half your customers leave every year and you're surviving on upsells to the remaining half. That's a fragile business.

Gross Revenue Retention strips out expansion and shows you pure retention health:

GRR = (Starting MRR - Contraction - Churn) / Starting MRR * 100

GRR can never exceed 100%. A GRR of 90%+ is strong. Below 80% means you have a serious retention problem that expansion is papering over.

You already have the numbers from the month-over-month query above. Just change the calculation:

-- Using the same mrr_changes CTE from the month-over-month query
 
SELECT
  month,
  SUM(starting_mrr)                                  AS starting_mrr,
  SUM(contraction)                                   AS contraction,
  SUM(churn)                                         AS churn,
  ROUND(
    (SUM(starting_mrr) - SUM(contraction) - SUM(churn))
    / NULLIF(SUM(starting_mrr), 0) * 100,
    1
  )                                                  AS grr_pct
FROM mrr_changes
GROUP BY month
ORDER BY month;

Report both side by side. NRR = 115%, GRR = 72% is a red flag that the headline number obscures. NRR = 105%, GRR = 95% is a much healthier business even though the NRR is lower.

NRR by segment

Blended NRR across your entire customer base hides the most important signal: where you retain revenue and where you don't. Enterprise customers typically show higher NRR (more seats to expand into, higher switching costs) while SMB runs lower (more price-sensitive, more likely to churn after a founder leaves).

This requires a customer dimension table. If you don't have one, build it — even a simple CSV mapping customer_id to segment.

-- Assumes a customers table with segment info
-- customers(customer_id, plan_tier, company_size, acquisition_channel)
 
WITH mrr_changes AS (
  SELECT
    curr.month,
    prev.customer_id,
    prev.mrr                                         AS starting_mrr,
    COALESCE(curr.mrr, 0)                            AS ending_mrr
  FROM customer_mrr prev
  LEFT JOIN customer_mrr curr
    ON  prev.customer_id = curr.customer_id
    AND curr.month = prev.month + INTERVAL '1 month'
  WHERE prev.mrr > 0
)
 
SELECT
  mc.month,
  c.plan_tier,
  COUNT(DISTINCT mc.customer_id)                     AS customers,
  SUM(mc.starting_mrr)                               AS starting_mrr,
  SUM(mc.ending_mrr)                                 AS ending_mrr,
  ROUND(
    SUM(mc.ending_mrr)::NUMERIC
    / NULLIF(SUM(mc.starting_mrr), 0) * 100,
    1
  )                                                  AS nrr_pct,
  -- GRR for comparison
  ROUND(
    LEAST(SUM(mc.ending_mrr), SUM(mc.starting_mrr))::NUMERIC
    / NULLIF(SUM(mc.starting_mrr), 0) * 100,
    1
  )                                                  AS grr_pct
FROM mrr_changes mc
JOIN customers c ON mc.customer_id = c.customer_id
GROUP BY mc.month, c.plan_tier
ORDER BY mc.month, c.plan_tier;

Swap c.plan_tier for c.company_size or c.acquisition_channel to slice differently. The most revealing cut I've seen: NRR by acquisition channel. If your Google Ads customers show 85% NRR while organic signups show 110%, you're paying to acquire customers who leave. That's a CAC payback problem disguised as a growth strategy.

Benchmarks and what the numbers mean

Quick reference, since these queries are useless without context:

• Best-in-class SaaS (Snowflake, Datadog): 130-170% NRR. Extreme usage-based expansion.
• Strong: 110-130%. Healthy expansion, low churn. Most venture-backed targets.
• Healthy: 100-110%. Customers are stable. You're not shrinking.
• Concerning: 90-100%. Revenue eroding. Expansion not keeping up.
• Critical: Below 90%. You need to fix retention before spending on acquisition.

GRR benchmarks are simpler: 90%+ is good, 85-90% is okay, below 80% is a problem.

From query to monitoring

Running these queries monthly in a SQL editor gives you the number. But NRR is a trailing indicator — by the time your 12-month NRR drops, the damage happened months ago. What you actually want is early warning: contraction signals, usage drops, failed payments that cascade into churn.

Fastero's Stripe integration pulls your subscription data automatically and tracks MRR movements in real time. You can build these exact queries against your synced data, or use the built-in revenue leak detection to catch the churn and contraction events that erode NRR before they compound. The cohort and segment queries above also work directly in Fastero's SQL editor against any connected data source.

The queries in this post give you the snapshot. Pair them with the MRR waterfall to see the movements over time, and Stripe subscription analysis for the raw data prep.

Try Fastero free — connect Stripe, build NRR dashboards, and get alerts when retention metrics shift. No credit card required.