How to calculate CAGR in SQL
Contents:
What CAGR is and why it shows up in every board deck
A finance lead at Stripe walks into a Monday review and says revenue went from 100 to 195 over the last three years. That sounds like an almost-doubling, but it is a total, not a rate. The board wants the rate — the number you can put next to inflation, next to a peer in the S-1, next to the same business one quarter ago. That number is CAGR.
CAGR is the constant yearly rate that takes a starting value to an ending value over a given period, assuming annual compounding. For 100 to 195 over three years, the answer is not the naive 31.7 percent and not 24 percent either — it is 24.93 percent compounded annually. It is the only single number that lets you compare growth of a SaaS line at Snowflake against an e-commerce category at Amazon against payment volume at Stripe over the same window.
In analyst interviews at Airbnb, DoorDash, and Notion, CAGR shows up in three flavors: a single headline number, a segment slice (which product or channel is compounding fastest), and an acquisition cohort cut (did the 2022 cohort generate more spend at year three than the 2021 cohort did at the same age). Each has a clean SQL pattern and its own set of mistakes.
The formula and a worked example
Three inputs: the value at the start of the window, the value at the end, and the years between them.
CAGR = (End / Start) ^ (1 / years) - 1Start equals 100, end equals 195, years equals three. The ratio 1.95 raised to the one-third power is 1.2493. Subtract one and you have 24.93 percent per year. Verify: 100 times 1.2493 cubed equals 195.
The formula has two failure modes you must handle in SQL up front. If Start is zero, the ratio is undefined — skip the row or fall back to the next non-zero point. If years is zero, the exponent is undefined too. Both silently return NULL or NaN depending on the engine, and the report ships with empty cells.
The base SQL query
Assume a table monthly_revenue(month, revenue) with one row per month. You want one number: CAGR across the full window. The query reads the first and last data point, computes elapsed years in fractional form, and applies the formula.
WITH endpoints AS (
SELECT
(SELECT revenue FROM monthly_revenue ORDER BY month ASC LIMIT 1) AS start_val,
(SELECT revenue FROM monthly_revenue ORDER BY month DESC LIMIT 1) AS end_val,
EXTRACT(EPOCH FROM (
(SELECT MAX(month) FROM monthly_revenue) -
(SELECT MIN(month) FROM monthly_revenue)
)) / (365.25 * 24 * 3600) AS years
)
SELECT
start_val,
end_val,
years,
POWER(end_val::NUMERIC / NULLIF(start_val, 0), 1.0 / NULLIF(years, 0)) - 1 AS cagr
FROM endpoints;Three choices in that query are deliberate. The epoch division by 365.25 * 24 * 3600 converts a date difference into fractional years, so a window of two years and one month becomes 2.083 rather than rounding to two. The NULLIF calls convert zeros into NULL so the engine returns NULL instead of throwing divide-by-zero. The ::NUMERIC cast keeps the calculation exact through the exponent, where Postgres POWER on floats can lose precision.
A simpler interview variant gives year-end values only — drop the epoch math and count distinct years minus one. The query above handles the realistic case where endpoints land on any month.
CAGR by segment
The next request after the headline is always the segment cut. Which product category is compounding fastest? Which channel? Which country? Same shape — start and end value per segment, divide, raise to the inverse power of years — scaled to one row per segment.
WITH yearly AS (
SELECT
category,
EXTRACT(YEAR FROM created_at) AS yr,
SUM(revenue) AS revenue
FROM orders
WHERE status = 'paid'
GROUP BY 1, 2
),
endpoints AS (
SELECT
category,
MIN(yr) AS start_yr,
MAX(yr) AS end_yr,
MAX(yr) - MIN(yr) AS years
FROM yearly
GROUP BY category
),
vals AS (
SELECT
e.category,
e.years,
(SELECT revenue FROM yearly WHERE category = e.category AND yr = e.start_yr) AS start_val,
(SELECT revenue FROM yearly WHERE category = e.category AND yr = e.end_yr) AS end_val
FROM endpoints e
)
SELECT
category,
start_val,
end_val,
years,
POWER(end_val::NUMERIC / NULLIF(start_val, 0), 1.0 / NULLIF(years, 0)) - 1 AS cagr
FROM vals
WHERE years > 0
ORDER BY cagr DESC;Watch the WHERE years > 0 filter. A segment that exists in only one calendar year — say, a product line launched this year — has zero years of history, and a CAGR there is meaningless. Drop it rather than letting it surface as NULL.
Also watch the calendar-versus-trailing choice. If your fiscal year ends in March but the data ends in November, comparing calendar-year totals compares a full year against a partial year and depresses the endpoint. Either snap to fiscal-year boundaries or use a rolling twelve-month window so every endpoint covers equal time.
CAGR by acquisition cohort
Cohort CAGR answers a question totals cannot: did the 2022 cohort, by year three, generate more average lifetime spend than the 2021 cohort did at the same age? That is a CAGR on top of a cohort pivot — a real Notion or Linear interview question.
WITH cohort_ltv AS (
SELECT
EXTRACT(YEAR FROM signup_date) AS cohort_year,
EXTRACT(YEAR FROM AGE(NOW(), signup_date)) AS years_since_signup,
AVG(total_spent) AS avg_ltv
FROM users
WHERE total_spent > 0
GROUP BY 1, 2
)
SELECT
cohort_year,
POWER(
MAX(CASE WHEN years_since_signup = 3 THEN avg_ltv END)::NUMERIC
/ NULLIF(MAX(CASE WHEN years_since_signup = 0 THEN avg_ltv END), 0),
1.0 / 3
) - 1 AS ltv_cagr_3y
FROM cohort_ltv
GROUP BY cohort_year
HAVING MAX(CASE WHEN years_since_signup = 3 THEN avg_ltv END) IS NOT NULL
ORDER BY cohort_year;The pivot uses a conditional aggregate to pull year-zero and year-three values onto the same row — the cleanest horizontal compare in Postgres without window functions. The HAVING clause drops cohorts too young to have a year-three point, because otherwise the 2024 row publishes NULL and somebody downstream reads it as zero growth.
For a richer cut, parameterize years_since_signup and run the query at years one, two, three, and four. The resulting LTV-by-cohort chart is what gets shipped to executives at Figma or Vercel when the argument is that the product is getting stickier.
Using CAGR for forecasts
The same formula runs backward: given a starting value and an assumed CAGR, project an ending value N years out. Finance asks for a 20 percent compounding plan and wants to see where revenue lands in five years.
SELECT
100 AS start_revenue,
0.20 AS expected_cagr,
5 AS years_ahead,
100 * POWER(1 + 0.20, 5) AS projected_revenue;
-- projected = 100 * 1.2^5 = 248.83Useful for plan targets, dangerous as a forecast. Compounding 20 percent for five years gets you to 2.49x; 30 percent gets you to 3.71x. Small input errors compound exponentially with horizon. In practice, run the projection at 15, 20, and 25 percent and label the output as a sensitivity, not a forecast.
Common pitfalls
The most common mistake is confusing CAGR with the arithmetic average of yearly growth rates. A business that grows 10 percent one year and shrinks 5 percent the next has an arithmetic mean of 2.5 percent, but a CAGR of (1.10 * 0.95)^(1/2) - 1 = 2.23 percent. The gap looks small here because the swings are small. With volatile metrics — gaming revenue at Roblox, crypto volume, ad spend in a recession — the arithmetic version overstates true compounded growth substantially.
The second trap is a tiny starting base. If a segment had one dollar of revenue in year one and a million in year three, the formula reports roughly 9900 percent CAGR — technically correct, substantively useless. Gate the result with a minimum-base filter (HAVING start_val > threshold) or report absolute dollars instead. The same applies to NULLs: NULLIF saves the query but you still need to drop or label those rows in the output.
The third trap is integer division in Postgres. The expression 1 / 3 returns zero, not 0.333. Write POWER(ratio, 1 / years) with both as integers and the exponent collapses to zero, so every row returns one and CAGR becomes zero. Cast at least one side to numeric — 1.0 / years or 1::NUMERIC / years — before the exponent. This is one of the most reported analyst bugs in production dashboards because it returns plausible-looking numbers rather than an error.
The fourth trap is a window that does not align with full years. January 2022 through November 2023 is 1.92 years, not two. Rounding to two understates the CAGR. The base query above uses the epoch trick to compute fractional years exactly; whenever the period is irregular, take the time difference in days and divide by 365.25.
The fifth trap is computing CAGR over too short a window. Half a year of growth annualized is a marketing number, not an analyst number. Two years is borderline; three or more is where the rate stabilizes. If somebody hands you eight months of data and asks for a CAGR, push back and quote quarter-over-quarter growth instead.
The sixth trap is seasonality at the endpoints. If start is January (low season) and end is December (peak), the CAGR is overstated by seasonality embedded in the endpoints rather than by real growth. Either compare Q4 against Q4 across years, or use a trailing twelve-month rolling sum at each endpoint so the seasonal pattern averages out.
Optimization tips
The CAGR query is cheap — two rows of an aggregated table — but the aggregation feeding it can be expensive at scale. If monthly_revenue is a derived table over a billion-row event log, materialize it. A pre-aggregated table with one row per month per unit fits in memory on Snowflake or Redshift and the CAGR query runs in milliseconds.
The segment-cut query benefits from an index on (category, yr) in Postgres or a clustering key on the same columns in Snowflake. Without it, each per-segment subquery scans the full yearly table once, which goes quadratic. A clustered index turns scans into seeks.
For the cohort query, AGE in Postgres is correct but slow because it computes calendar-aware year differences. At scale, replace EXTRACT(YEAR FROM AGE(NOW(), signup_date)) with EXTRACT(YEAR FROM NOW()) - EXTRACT(YEAR FROM signup_date) — approximate but two orders of magnitude faster, and fine for cohort grouping because the bucket boundary is a calendar year either way.
Related reading
- SQL window functions interview questions
- How to calculate ARR Growth in SQL
- How to calculate burn rate in SQL
- How to calculate churn in SQL
If you want to drill SQL questions like this every day, NAILDD is launching with 500+ SQL problems across exactly this pattern.
FAQ
Can I compute CAGR on monthly data instead of yearly?
You can, but the formula changes. The expression (End/Start)^(1/months) - 1 gives a compound monthly growth rate, not a CAGR. To convert to an annual rate, raise the monthly growth factor to the twelfth power, equivalent to (End/Start)^(12/months) - 1. The conversion is mathematically clean but assumes the monthly growth pattern is stable. With heavy seasonality, the converted number is biased. In practice, aggregate to yearly totals first and run the standard formula on those.
What is the difference between CAGR and IRR?
CAGR is the geometric growth rate of a single value over time — start to end, no intermediate cash flows. IRR is the discount rate that makes the net present value of a series of cash flows equal to zero. CAGR is the right tool for a metric like ARR, MAU, or category revenue. IRR is the right tool for evaluating an investment with inflows and outflows across periods, like a private equity deal or a real estate project. Reaching for IRR when you want CAGR overcomplicates a clean number; reaching for CAGR when you want IRR ignores cash-flow timing.
What is a good CAGR?
It depends on industry and stage. Mature retail at 5 to 10 percent is healthy. Public SaaS at 20 to 30 percent is a market leader. Early-stage SaaS at 100 to 300 percent is on a venture trajectory. Telecom or utility at 2 to 4 percent is fine because the base is enormous. The number is only meaningful against three benchmarks: inflation, the closest public comparable, and the same business one year ago.
What if CAGR comes out negative?
Negative CAGR is a valid result — the metric is shrinking on a compounded basis. The formula handles it cleanly: (80/100)^(1/2) - 1 = -10.56 percent per year. The only thing to be careful about is sign communication in the report. A column labeled "growth" with negative values is more honest if relabeled "change" or shown with an explicit minus sign, because small negative numbers get parsed as rounding artifacts otherwise.
Can I use AVG in SQL to compute CAGR?
No. AVG(yearly_growth_rate) computes the arithmetic mean, which equals CAGR only when every year has identical growth. The mean overstates compounded growth whenever yearly rates vary, and the gap widens with variance. Use the closed-form formula with POWER — there is no shortcut through AVG.