How to calculate Gross Dollar Retention in SQL

What is GDR and why it matters

Gross Dollar Retention (GDR) is the cleanest single number for the health of your existing book of business. It answers one question: out of every dollar of MRR you started the period with, how many did you keep — ignoring upsells and cross-sells? Unlike Net Dollar Retention, which can hide a leaky bucket behind aggressive expansion, GDR has a hard ceiling at 100%. If a customer downgrades, churns, or refunds, GDR goes down. Period.

Picture this scenario. It is Sunday night and your CFO pings you on Slack: the board meeting is Tuesday and they want to see whether the 118% NDR the sales team has been celebrating is masking a churn problem in the long tail. You have until Monday morning to produce a chart that splits NDR into its components and shows what the existing base would retain without the expansion sugar. That is a GDR query. Every SaaS analyst at Stripe, Notion, Linear, Figma, or Databricks has written some version of it, and it shows up regularly in data analyst interviews.

The reason GDR matters for product-market fit conversations is simple. You can goose NDR with a price increase or a packaging change. You cannot goose GDR — it is a pure measure of whether the customers you already have keep paying the same amount they paid twelve months ago. Investors at growth funds look at GDR before they look at NDR for exactly this reason. A GDR of 95%+ for enterprise or 85%+ for SMB gets a SaaS round priced at a premium multiple. A GDR of 75% with NDR of 110% gets you a polite pass.

The SQL formula

The math is straightforward. Take every customer who was paying you on the start date. Compare what they were paying then to what they are paying on the end date. Cap the end value at the start value — that is what "gross" means, no upside credit. Sum the capped end value, divide by the sum of the start MRR, multiply by one hundred. That is GDR.

WITH start_cohort AS (
    SELECT customer_id, mrr AS start_mrr
    FROM subscription_state
    WHERE state_date = '2025-05-01' AND status = 'active'
),
end_state AS (
    SELECT customer_id, mrr AS end_mrr
    FROM subscription_state
    WHERE state_date = '2026-05-01'
),
joined AS (
    SELECT
        s.customer_id,
        s.start_mrr,
        COALESCE(e.end_mrr, 0) AS end_mrr,
        LEAST(s.start_mrr, COALESCE(e.end_mrr, 0)) AS retained_mrr
    FROM start_cohort s
    LEFT JOIN end_state e USING (customer_id)
)
SELECT
    SUM(start_mrr) AS total_start_mrr,
    SUM(retained_mrr) AS total_retained_mrr,
    SUM(retained_mrr)::NUMERIC * 100 / NULLIF(SUM(start_mrr), 0) AS gdr_pct
FROM joined;

If a customer grew from $100 to $200, the retained amount is $100 — we throw away the $100 of expansion. If a customer dropped from $100 to $50, the retained amount is $50 — the loss is real and we count it. If a customer churned entirely, the LEFT JOIN produces a NULL end MRR, the COALESCE turns it into zero, and the retained amount is zero. The NULLIF in the denominator protects against division-by-zero on an empty cohort.

One subtlety: subscription_state is assumed to be a daily or monthly snapshot of every active subscription with its current MRR. If your warehouse only stores event-style billing data, materialize this snapshot first with a window function over plan-change events. The GDR formula on top is identical.

GDR vs NDR side by side

The most useful version of this query is the one that produces GDR, NDR, and the expansion contribution in a single result row. That way you can drop it into a dashboard and instantly see the gap between gross and net — which is, by definition, how much expansion is doing for you.

SELECT
    SUM(start_mrr) AS start_mrr,
    -- GDR: caps end at start, no expansion credit
    SUM(LEAST(start_mrr, COALESCE(end_mrr, 0)))::NUMERIC * 100
        / NULLIF(SUM(start_mrr), 0) AS gdr_pct,
    -- NDR: end_mrr as-is, expansion included
    SUM(COALESCE(end_mrr, 0))::NUMERIC * 100
        / NULLIF(SUM(start_mrr), 0) AS ndr_pct,
    -- The gap is what expansion is buying you
    (SUM(COALESCE(end_mrr, 0)) - SUM(LEAST(start_mrr, COALESCE(end_mrr, 0))))::NUMERIC
        * 100 / NULLIF(SUM(start_mrr), 0) AS expansion_contribution_pct
FROM joined;

If you run this and get NDR 120% with GDR 85%, you have a 35-point expansion contribution — the "expansion is healing churn" pattern. Not necessarily bad — many of the best public SaaS comps run this way — but it is a fragile equilibrium. Sales has to keep landing upsells at the same rate to keep the headline number above 100%. The day expansion slows, the gross leak shows up immediately. A healthier shape is NDR above 110% with GDR above 90%: you are growing accounts and keeping the ones you already have.

Breaking GDR down by segment

A single firmwide GDR number is almost always misleading. The retention behavior of a $50/month self-serve customer and a $50,000/month enterprise customer have nothing in common. Segment the cohort.

SELECT
    plan_segment,
    SUM(start_mrr) AS start_mrr,
    SUM(LEAST(start_mrr, COALESCE(end_mrr, 0)))::NUMERIC * 100
        / NULLIF(SUM(start_mrr), 0) AS gdr_pct
FROM joined
GROUP BY plan_segment
ORDER BY start_mrr DESC;

The pattern you will almost certainly see:

Enterprise GDR shows low variance month to month because contracts lock in. SMB GDR floats with much higher variance — customers can cancel with one click and they react sharply to macro conditions. If your enterprise GDR is below 90%, you almost certainly have a customer success problem, not a product problem. If your SMB GDR is below 75%, you have a product-market fit problem in the low-end packaging.

You can also segment by acquisition channel, customer industry, tenure bucket, or usage tier. The same LEAST trick applies in each grouping — you only change what you group by.

Common pitfalls

The most common GDR mistake is accidentally including expansion. This happens when an analyst writes SUM(end_mrr) / SUM(start_mrr) because that is the formula they remember, and they label the result "GDR" without thinking about the cap. The output is mathematically NDR. The fix is the LEAST(start_mrr, end_mrr) cap.

Another trap is putting the arguments to LEAST in the wrong order — or, worse, using GREATEST instead. LEAST(start, end) caps growth and lets shrinkage through, which is what you want. GREATEST(start, end) does the opposite: it caps shrinkage and lets growth through, which gives you an expansion-only metric. That metric has a name (it is essentially gross expansion), but it is not GDR. Always double-check the function name and the argument order before you ship the query.

A third pitfall is using historical price instead of current MRR when a customer downgrades inside the period. If your subscription_state table records the price the customer signed up at instead of the price they are paying right now, downgrades become invisible — the customer looks like they are still paying $200 when they are actually paying $100. GDR will look artificially high. The fix is to make sure the state table reflects the live committed MRR at each snapshot, applying any mid-period plan changes. This is also why a daily snapshot is more accurate than a monthly one for fast-moving SMB businesses.

The fourth trap is conflating dollar retention with customer retention. GDR weights every customer by what they pay. Customer churn rate weights every customer the same. A business can have 95% GDR and 70% logo retention if the customers who churn are small and the ones who stay are big. Both numbers matter, but they do not add up to anything. Do not write "GDR + churn = 100%" in a board deck.

The fifth pitfall is computing GDR over a window that is too short. One-month GDR is almost always above 97% simply because most customers do not churn in any given month — the metric has no signal. The industry standard is trailing-twelve-month GDR, which captures a full annual renewal cycle for most enterprise contracts and smooths out the monthly noise of consumer-style SMB plans. If you have to report monthly GDR for an operational dashboard, do it as a rolling twelve-month window, not a single-month snapshot.

Optimization tips

For large warehouses, the bottleneck is almost always the join between start_cohort and end_state. Index or cluster the underlying snapshot table on customer_id and state_date so each subquery does a single range scan instead of a full table scan. In Snowflake, that means clustering on (state_date, customer_id). In BigQuery, partition by state_date and cluster on customer_id. In Postgres or Redshift, add a compound index on the same two columns.

If you run GDR daily across many cohorts and segments, materialize an intermediate table that already has the LEAST(start, end) calculation per customer per period. That way the dashboard query is a simple SUM and GROUP BY over a thin table, which is two orders of magnitude faster than recomputing the join every time someone opens the dashboard. In dbt, this is a classic incremental model. In Databricks, a Delta table with ZORDER BY customer_id works well.

For very high cardinality, pre-compute the capped value in a CTE instead of nesting LEAST inside the aggregate. Some optimizers (older Redshift, MySQL) do not push LEAST down efficiently when it sits inside SUM. Doing the cap first, then summing, is functionally identical and reliably faster.

Related reading

• How to calculate contraction MRR in SQL
• How to calculate expansion MRR in SQL
• How to calculate churn in SQL
• How to calculate ARR in SQL

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

FAQ

Which matters more, GDR or NDR?

They measure different things and serious finance teams look at both. NDR tells you whether the business is growing inside its existing customer base — it is the headline number that drives valuation multiples. GDR tells you whether the customer base itself is healthy at the dollar level — it is the diagnostic number that tells you if NDR is built on a solid foundation or on aggressive upsell motion. The honest read is to look at the gap. A wide gap (NDR 120%, GDR 80%) means expansion is doing the work and churn is leaking. A narrow gap (NDR 115%, GDR 105%) — which is essentially impossible since GDR is capped at 100%, but as close as you can get — means both the base is sticky and the customers are growing.

Can GDR ever exceed 100%?

No, never. That is the definition. If your query produces a GDR above 100%, you have a bug — usually the missing LEAST cap, occasionally a join that double-counts customers across plan changes, occasionally a snapshot table that reports the wrong MRR for a given date. Treat any GDR above 100% as a data quality alarm, not a celebration.

What counts as a "good" GDR?

Benchmarks vary by segment and stage. For enterprise SaaS, 95% and above is the bar that investors at growth funds want to see. For mid-market, 90%–94% is healthy. For SMB and prosumer, 85% is good and 90% is best-in-class — anything below 80% suggests a product-market fit issue in the low end. For developer tools and infrastructure (think Vercel, Stripe-style usage-based products), 90%+ at the dollar level is typical because usage-based revenue compounds naturally with customer growth.

How do I diagnose a low GDR?

Decompose the gross leak into its two parts: pure logo churn (customer left entirely) and downgrade (customer is still here but paying less). The SQL is a CASE statement: customers with end_mrr = 0 are churn, customers with end_mrr > 0 AND end_mrr < start_mrr are contraction. Once you know which one dominates, you know where to look. Churn-driven GDR loss points to onboarding, value realization, or competitive displacement. Contraction-driven loss points to packaging, seat expansion bait-and-switch, or usage decline. Customer success teams at Notion, Linear, and Figma run this decomposition monthly.

Can I apply GDR to annual contracts using ARR instead of MRR?

Yes, identically. Substitute ARR for MRR everywhere in the formula. The cap, the join, and the aggregation are the same. The main thing to watch is that your snapshot table records the right ARR at the right time — for annual contracts, mid-period changes are rare but they happen (early renewals, contract resizings, upsell amendments). If you are mixing monthly and annual customers in the same cohort, normalize everyone to MRR (annual divided by twelve) before you compute GDR. Otherwise the annual customers will dominate the numerator and denominator and you will not see what is happening in the monthly book.

Should I include free customers in the GDR cohort?

No. GDR is a paid-base metric. Free customers have $0 MRR and contribute nothing to the numerator or denominator. Filter to status = 'active' AND mrr > 0 in the start cohort. Free-to-paid conversion is a separate question with a separate query.