March MRR Reconciliation Across SQL and a Price Catalog
A SaaS finance team asks for March MRR from two sources: a PostgreSQL table subscriptions(subscription_id, account_id, plan_id, start_ts, end_ts, status) with 120,000 rows, and plan_catalog.csv(plan_id, monthly_price, currency, effective_from) with 14,000 rows because prices changed over time. Timestamps are stored as UTC except 3,000 rows loaded from a legacy system that used local time (UTC-5). Some subscriptions have end_ts earlier than start_ts; some cancelled rows have NULL end_ts. An analyst merged the two tables on plan_id only, filtered status='active' and start_ts <= '2025-03-31', then summed monthly_price, producing $1.51M. Finance's independent report says $1.42M for March 31. The CSV contains duplicate plan_id values for price changes but no duplicate exact rows. Stakeholders want a reproducible query and an explanation of the gap.
1. Diagnose the $90k overstatement. Identify merge, time, status, and validation issues and explain how each changes the result.
Read the worked answer
The gap is likely a fan-out plus a time-window error, not a finance mistake. Merging on plan_id only joins each subscription to every historical price row for that plan; if a plan has 2-3 effective price rows, the subscription is counted 2-3 times. That alone can explain a large overstatement.
The filter start_ts <= 2025-03-31 does not exclude subscriptions that ended before that date; status='active' is not enough because cancelled rows can have NULL end_ts, and end_ts < start_ts is invalid. A point-in-time MRR must require start_ts <= date AND (end_ts IS NULL OR end_ts > date), with invalid intervals quarantined. The 3,000 legacy local-time rows can shift the March/April boundary by five hours, so date filtering before UTC conversion misclassifies some subscriptions. Currency may also be summed without conversion. Validate one price row per subscription after the join and compare distinct subscription counts before and after.
2. Provide a SQL approach that computes point-in-time MRR correctly and list reconciliation checks.
Read the worked answer
Use an as-of join. Compute the next effective date per plan, then join each eligible subscription to the price row whose interval contains 2025-03-31. Convert timestamps to UTC first. Sketch:
WITH price AS (
SELECT plan_id, monthly_price, currency, effective_from,
LEAD(effective_from) OVER (PARTITION BY plan_id ORDER BY effective_from) AS next_eff
FROM plan_catalog
),
sub AS (
SELECT * FROM subscriptions
WHERE start_ts <= TIMESTAMPTZ '2025-03-31 23:59:59+00'
AND (end_ts IS NULL OR end_ts > TIMESTAMPTZ '2025-03-31 23:59:59+00')
AND (end_ts IS NULL OR end_ts >= start_ts)
)
SELECT SUM(p.monthly_price) AS mrr
FROM sub s
JOIN price p
ON p.plan_id = s.plan_id
AND p.effective_from <= DATE '2025-03-31'
AND (p.next_eff IS NULL OR p.next_eff > DATE '2025-03-31');
Check row counts before and after the join, distinct subscription_id, exactly one price row per subscription, no negative or zero intervals, currency conversion, and reconciliation by plan against finance. Use a transaction or a view so the result is reproducible.