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…