Drill 12 of 12 in SaaS questions
0

Interview Question Bank · Cohort snapshots · conditional SUM · revenue bridge

12. Net revenue retention for a cohort

HardSaaSQuestion #120

Concept: Cohort snapshots · conditional SUM · revenue bridgeshow

Net revenue retention follows a fixed cohort: the accounts paying at the start. Take each account's MRR on the start date and on the end date (0 if it churned), then split the change into expansion (went up), contraction (went down but still paying) and churn (went to 0). NRR is ending MRR over starting MRR for that cohort only; revenue from accounts that joined later is new business and stays out.

Example (different data):

WITH m AS (
  SELECT member_id,
    SUM(fee) FILTER (WHERE year = 2024) AS fee_2024,
    COALESCE(SUM(fee) FILTER (WHERE year = 2025), 0) AS fee_2025
  FROM club_fees GROUP BY member_id
)
SELECT ROUND(100.0 * SUM(fee_2025) / SUM(fee_2024), 1) AS retention_pct
FROM m WHERE fee_2024 IS NOT NULL;

Your task

Tallyboard's board deck needs net revenue retention for the accounts that were paying on 2025-01-01, measured on 2025-06-01. A subscription is running on a day when start_date <= day and end_date is NULL or after it.

For the cohort of accounts with a subscription running on 2025-01-01, compare each account's MRR that day with its MRR on 2025-06-01 (0 if none). Return one row: starting_mrr, expansion_mrr (total increase from accounts that grew), contraction_mrr (total decrease from accounts that shrank but still pay), churned_mrr (starting MRR of accounts at 0), ending_mrr and nrr_pct (ending as a % of starting, rounded to 1 decimal).

Starting Postgres in your browser…

Checking your access…