How to Calculate Net Revenue Retention in SQL Without Fooling Yourself

[Malek Damiri]

Net revenue retention is the number a board asks about first and the one most often computed wrong. Not maliciously. The two most common shortcuts look reasonable in a meeting and quietly turn a business that is shrinking its existing customers into one that appears to be growing them.

In the example below, the honest answer is 89%. The two shortcuts return 103% and 118%.

The definition, in one sentence​

Take every customer who paid you twelve months ago. NRR is what those same customers pay you now, divided by what they paid you then.

Three things follow from that sentence, and every mistake in this post breaks one of them:

1. The cohort is fixed at the start. Customers who arrived since then are not in it.
2. Customers who left are still in it, at zero.
3. It is a revenue ratio, not a customer count. Counting customers gives you logo retention, which is a different number.

Gross revenue retention (GRR) is the stricter cousin. It caps each customer's current revenue at their starting revenue, so expansion cannot hide churn. GRR can never be above 100%.

The data you need​

One row per customer per month of recurring revenue:

customer_monthly_revenue (customer_id, revenue_month, revenue)

Before any of the queries below, make sure that table is:

• Recurring only. Setup fees, one-off services, and true-ups are not retention.
• Net of credits. A refund booked in a later month makes that month look like contraction.
• In one currency at a fixed rate. Otherwise a strong dollar shows up as churn.
• One row per customer and month. If you have one row per subscription, aggregate first. The trend query later does this for you.

Here is a small sample that you can run as-is in Snowflake or DuckDB:

create or replace temp table customer_monthly_revenue as
select customer_id, revenue_month::date as revenue_month, revenue::numeric(12, 2) as revenue
from (values
  ('acme',  '2024-09-01', 1000), ('acme',  '2025-09-01', 1300),
  ('birch', '2024-09-01',  800), ('birch', '2025-09-01',  600),
  ('cedar', '2024-09-01',  500),
  ('delta', '2024-09-01', 1200), ('delta', '2025-09-01', 1200),
  ('fjord', '2024-09-01',  300), ('fjord', '2025-09-01',  300),
  ('ember', '2025-09-01',  700),
  ('gale',  '2025-09-01',  400)
) as t(customer_id, revenue_month, revenue);

Acme expanded, Birch contracted, Cedar left, Delta and Fjord stayed flat, and Ember and Gale are new this year.

The query​

with params as (
  select '2025-09-01'::date as as_of_month
),
base as (
  select r.customer_id, r.revenue as base_revenue
  from customer_monthly_revenue r
  cross join params p
  where r.revenue_month = (p.as_of_month - interval '12 months')::date
    and r.revenue > 0
),
current_month as (
  select r.customer_id, r.revenue as current_revenue
  from customer_monthly_revenue r
  cross join params p
  where r.revenue_month = p.as_of_month
),
cohort as (
  select
    b.customer_id,
    b.base_revenue,
    coalesce(c.current_revenue, 0) as current_revenue
  from base b
  left join current_month c on c.customer_id = b.customer_id
)
select
  count(*) as cohort_customers,
  sum(base_revenue) as base_revenue,
  sum(current_revenue) as current_revenue,
  round(sum(current_revenue) / sum(base_revenue), 3) as nrr,
  round(sum(least(current_revenue, base_revenue)) / sum(base_revenue), 3) as grr
from cohort;

cohort_customers  base_revenue  current_revenue    nrr    grr
               5         3,800            3,400  0.895  0.816

Two lines do all the work. The base CTE fixes the cohort to customers who paid in the starting month, so Ember and Gale never enter. The left join with coalesce(..., 0) keeps Cedar in the cohort at zero instead of letting it vanish.

Make the bridge add up​

A single ratio invites the question "why?" The bridge answers it, and it doubles as a test: starting revenue plus expansion, minus contraction, minus churn, has to equal ending revenue exactly. If it doesn't, something in the cohort logic is wrong.

Reuse the same params, base, current_month, and cohort CTEs and replace the final select:

select
  sum(base_revenue) as starting_revenue,
  sum(case when current_revenue > base_revenue
           then current_revenue - base_revenue else 0 end) as expansion,
  sum(case when current_revenue > 0 and current_revenue < base_revenue
           then base_revenue - current_revenue else 0 end) as contraction,
  sum(case when current_revenue = 0 then base_revenue else 0 end) as churn,
  sum(current_revenue) as ending_revenue
from cohort;

starting_revenue  expansion  contraction  churn  ending_revenue
           3,800        300          200    500           3,400

3,800 + 300 - 200 - 500 = 3,400. Put this table under the NRR number on the slide and most follow-up questions go away.

Two ways to get it wrong​

Mistake 1: total revenue now over total revenue then.

select round(
  sum(case when revenue_month = '2025-09-01' then revenue end)
  / sum(case when revenue_month = '2024-09-01' then revenue end), 3) as not_nrr
from customer_monthly_revenue;

This returns 1.184. It counts Ember and Gale, who weren't customers a year ago. That's total revenue growth, a fine number, but it says nothing about whether existing customers stayed and grew.

Mistake 2: an inner join.

select round(sum(c.revenue) / sum(b.revenue), 3) as also_not_nrr
from customer_monthly_revenue b
join customer_monthly_revenue c
  on c.customer_id = b.customer_id
 and c.revenue_month = '2025-09-01'
where b.revenue_month = '2024-09-01';

This returns 1.030. It's the subtler one because the cohort logic looks right. But Cedar has no row in September 2025, so the inner join drops Cedar from both the numerator and the denominator, and the churn disappears. Customers who leave rarely leave a row behind saying so.

A monthly trend​

One month is a data point. Finance will want the trend. This version aggregates to one row per customer and month first, then computes NRR and GRR for every month that has a year of history behind it:

with monthly as (
  select
    customer_id,
    date_trunc('month', revenue_month)::date as revenue_month,
    sum(revenue) as revenue
  from customer_monthly_revenue
  group by 1, 2
),
pairs as (
  select m.revenue_month as as_of_month, b.customer_id, b.revenue as base_revenue
  from (select distinct revenue_month from monthly) m
  join monthly b
    on b.revenue_month = (m.revenue_month - interval '12 months')::date
   and b.revenue > 0
)
select
  p.as_of_month,
  count(*) as cohort_customers,
  round(sum(coalesce(c.revenue, 0)) / sum(p.base_revenue), 3) as nrr,
  round(sum(least(coalesce(c.revenue, 0), p.base_revenue)) / sum(p.base_revenue), 3) as grr
from pairs p
left join monthly c
  on c.customer_id = p.customer_id
 and c.revenue_month = p.as_of_month
group by p.as_of_month
order by p.as_of_month;

The left join is doing the same job as before: a customer in the base month with no revenue in the current month counts as zero.

If your revenue is usage-based​

Comparing a single month to a single month works for subscriptions. For usage-based revenue, one quiet month can look like contraction and one busy month like expansion. Compare three-month averages instead. Build a smoothed table on a full customer-by-month grid, so a month with no usage counts as zero rather than disappearing, then point the trend query at it in place of monthly:

months as (select distinct revenue_month from monthly),
customers as (select distinct customer_id from monthly),
smoothed as (
  select
    c.customer_id,
    s.revenue_month,
    coalesce(sum(m.revenue), 0) / 3 as revenue
  from customers c
  cross join months s
  left join monthly m
    on m.customer_id = c.customer_id
   and m.revenue_month between (s.revenue_month - interval '2 months')::date
                           and s.revenue_month
  group by c.customer_id, s.revenue_month
)

Say on the slide that the numbers are smoothed. Smoothed and unsmoothed NRR are both legitimate, but they're not interchangeable.

Edge cases to decide once and write down​

• Reactivations. A customer who paid in the base month, paused, and came back is retained. A customer who left before the base month and came back is new. The queries above already behave this way.
• Free to paid. A customer with zero revenue in the base month isn't in the cohort, so an upgrade from a free plan shows up as new revenue, not expansion.
• Merged or split accounts. If customer IDs change when accounts are consolidated, map old IDs to new ones before you start, or the bridge will show a churn and a new customer that are really the same company.
• Annual contracts. Use monthly recurring revenue, not invoice amounts, or every renewal month looks like a spike.

None of these are hard to handle. The hard part is choosing one answer for each, writing it next to the metric, and using it every time.

What to put on the slide​

NRR, GRR, and the bridge, with a one-line definition under them: "Revenue from customers active twelve months ago, now versus then, recurring revenue only, constant currency." If you can't write that line, the number above it isn't finished yet.