Interview Question Bank · Range join · open-ended periods · generate_series
10. MRR by month from subscription periods
HardSaaSQuestion #118
Concept: Range join · open-ended periods · generate_seriesshow
Subscriptions are periods, not events: one row covers many months. To get a monthly snapshot, generate the months and join every period that covers each month's first day: start <= month AND (end IS NULL OR end > month). An open-ended row has NULL as its end, and a plain comparison with NULL is never true, so it needs the IS NULL branch. Treat the end as exclusive so a plan that ends the day another begins isn't counted twice.
Example (different data):
SELECT d.day::date, COUNT(*) AS guests FROM generate_series(TIMESTAMP '2025-08-01', TIMESTAMP '2025-08-07', INTERVAL '1 day') AS d(day) JOIN hotel_stays s ON s.check_in <= d.day AND (s.check_out IS NULL OR s.check_out > d.day) GROUP BY d.day ORDER BY d.day;
Your task
Tallyboard's finance team reports MRR as a snapshot on the 1st of each month: the sum of mrr for every subscription running that day. end_date is the first day a plan no longer applies, and NULL means it is still running.
For each month from January to June 2025, return month (YYYY-MM), paying_accounts (distinct accounts with a subscription running on the 1st) and mrr (their total mrr). Sort by month.
Starting Postgres in your browser…
Checking your access…