Case 6 — SaaS Metrics: MRR, MRR Growth, Net Revenue Retention

"From subscriptions(customer_id, plan_id, subscription_month, monthly_revenue) — one row per customer per month they were active — compute monthly MRR for the last 12 months, month-over-month MRR growth, and net revenue retention by acquisition cohort."

This is the capstone. It pulls window functions, cohort logic, and growth-rate patterns together. If you handle it cleanly, you've demonstrated enough SQL judgment to pass an analyst interview at a SaaS shop.

---

Schema

subscriptions(customer_id, plan_id, subscription_month, monthly_revenue)

One row per (customer, month) when active. monthly_revenue is what they paid that month (handles upgrades/downgrades naturally — different months can have different revenue).

Part 1 — MRR by month

SELECT
    subscription_month,
    SUM(monthly_revenue) AS mrr,
    COUNT(DISTINCT customer_id) AS active_customers
FROM subscriptions
WHERE subscription_month >= DATE_TRUNC('month', CURRENT_DATE) - INTERVAL '12 months'
  AND subscription_month <  DATE_TRUNC('month', CURRENT_DATE)
GROUP BY subscription_month
ORDER BY subscription_month;

12-line warm-up. The interviewer is checking baseline.

Part 2 — Month-over-month MRR growth

WITH monthly_mrr AS (
    SELECT
        subscription_month,
        SUM(monthly_revenue) AS mrr
    FROM subscriptions
    WHERE subscription_month >= DATE_TRUNC('month', CURRENT_DATE) - INTERVAL '13 months'
      AND subscription_month <  DATE_TRUNC('month', CURRENT_DATE)
    GROUP BY subscription_month
)
SELECT
    subscription_month,
    mrr,
    LAG(mrr) OVER (ORDER BY subscription_month) AS prev_mrr,
    ROUND(100.0 * (mrr - LAG(mrr) OVER (ORDER BY subscription_month))
        / NULLIF(LAG(mrr) OVER (ORDER BY subscription_month), 0), 2) AS mom_growth_pct
FROM monthly_mrr
ORDER BY subscription_month;

Note the 13-month pull — one extra month back so the first month in the output has a previous-month value for LAG.

Part 3 — Net Revenue Retention (NRR)

The definition: for each acquisition cohort, what % of their initial month's MRR are they still paying N months later? Expansions/upgrades push NRR above 100%; downgrades/churn pull it below.

This is the hard one.

Step a — Define cohort and initial MRR per customer

WITH first_month AS (
    SELECT
        customer_id,
        MIN(subscription_month) AS cohort_month
    FROM subscriptions
    GROUP BY customer_id
),
cohort_initial_mrr AS (
    SELECT
        c.customer_id,
        c.cohort_month,
        s.monthly_revenue AS initial_mrr
    FROM first_month c
    JOIN subscriptions s
        ON s.customer_id = c.customer_id
        AND s.subscription_month = c.cohort_month
)
SELECT * FROM cohort_initial_mrr;

Each row: customer X joined in cohort Y with initial MRR Z.

Step b — For each month, sum each cohort's MRR

WITH first_month AS (...),
cohort_initial_mrr AS (...),
cohort_mrr_by_month AS (
    SELECT
        cim.cohort_month,
        s.subscription_month,
        SUM(s.monthly_revenue) AS cohort_mrr_this_month,
        SUM(cim.initial_mrr) FILTER (WHERE s.subscription_month = cim.cohort_month) AS cohort_initial_mrr_total
    FROM cohort_initial_mrr cim
    JOIN subscriptions s ON s.customer_id = cim.customer_id
    GROUP BY cim.cohort_month, s.subscription_month
)
SELECT * FROM cohort_mrr_by_month;

For each (cohort_month, observation_month), sum the MRR of all surviving customers in that cohort.

Step c — Pin the initial cohort MRR and compute NRR

WITH first_month AS (...),
cohort_initial_mrr AS (...),
cohort_mrr_by_month AS (...),
cohort_initial_total AS (
    SELECT cohort_month, SUM(initial_mrr) AS initial_total
    FROM cohort_initial_mrr
    GROUP BY cohort_month
)
SELECT
    cmm.cohort_month,
    cmm.subscription_month,
    EXTRACT(MONTH FROM AGE(cmm.subscription_month, cmm.cohort_month))::int AS months_in,
    cmm.cohort_mrr_this_month,
    cit.initial_total,
    ROUND(100.0 * cmm.cohort_mrr_this_month / NULLIF(cit.initial_total, 0), 1) AS nrr_pct
FROM cohort_mrr_by_month cmm
JOIN cohort_initial_total cit ON cit.cohort_month = cmm.cohort_month
WHERE cmm.subscription_month >= cmm.cohort_month
ORDER BY cmm.cohort_month, months_in;

nrr_pct over time per cohort. A healthy SaaS shows NRR > 100% by month 12 (expansions exceed churn). A struggling one shows 60-80% (churn dominates).

What to say at the end

You: "MRR is the SUM. MoM growth is LAG + arithmetic. NRR is the multi-step one — cohort definition, sum-per-cohort-per-month, divide by initial cohort total. The hardest bit is being precise about which customers are 'still in the cohort' — I included them as long as they have a subscription_month row, which captures both expansions and downgrades. Net-of-churn is automatic because churned customers stop having rows."

You (bonus): "I'd also note: NRR by month is informative; the headline number is usually NRR at month 12, often called 'annual NRR'. The board cares about the 12-month number, not the curve."

Common follow-ups

Follow-up 1: "Break out NRR by acquisition channel."

Add an acquisition_channel column to first_month and GROUP BY it everywhere downstream.

Follow-up 2: "What's the difference between NRR and GRR (Gross Retention Rate)?"

GRR caps each customer's contribution at their initial MRR — expansions don't count. To compute GRR, take LEAST(monthly_revenue, initial_mrr) per customer per month, then sum and divide.

You: "NRR includes expansion revenue; GRR is the floor — churn/downgrade only. SaaS shops report both. NRR > GRR by definition. If NRR is 110% and GRR is 88%, you have 22% churn-and-downgrade and 22% expansion that nets out positive."

Follow-up 3: "Performance — 100M subscription rows."

• Index on (customer_id, subscription_month) — primary access path.
• Pre-aggregate cohort_initial_mrr into a materialized table refreshed monthly. It only changes on the 1st.
• The cohort_mrr_by_month CTE is the expensive step; consider a daily-refreshed summary table.

Self-check MCQs