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

# 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 ```sql 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) - 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 ```sql 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 ```sql 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 ```sql 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 usual