Drill 10 of 12 in SaaS questions
0

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…