MRR Calculation Pitfalls in Postgres for Subscription Products
Postgres won't catch when your MRR math divides wrong buckets or measures the wrong time period.
Postgres will let a subscription business query its billing data in whatever shape it wants, and that freedom is the reason Monthly Recurring Revenue numbers go wrong so often and so quietly. A query against a subscriptions or invoices table runs cleanly, returns a tidy dollar figure, and gives no indication that the figure fails to match what MRR is supposed to mean. The query succeeds syntactically while failing semantically: nothing in Postgres checks whether an annual contract was amortized, whether a trial got filtered out, or whether a discount was applied before the sum ran. These errors are common and occur even in standard billing setups. They are the default outcome of writing MRR SQL without an explicit checklist of what counts, what gets excluded, and what needs to be divided down to a monthly amount first.
What MRR Is Measuring
The arithmetic behind MRR is close to trivial. Deciding which rows belong in the numerator, and which ones need to be transformed before they go in, is where the real difficulty sits, and billing tables will not make that judgment on a team's behalf. MRR is active subscription revenue normalized to a single month. ARR is simply MRR multiplied by 12, expressing the current run rate on an annual basis. Neither figure is a forecast, and neither represents cash actually collected: both describe a predictable rate, not a ledger balance.
That single aggregate is made up of a set of distinct movements that each require their own definition of which rows qualify: New Business, Expansion, Contraction, Churn, Reactivation, and Neutral. A customer upgrading a plan contributes to Expansion; a customer downgrading contributes to Contraction; a customer who cancels contributes to Churn; a customer who left and came back contributes to Reactivation, not New Business. Six categories, each with its own qualification criteria, all collapsing into one number on a dashboard. Billing tables record what happened financially: an invoice was issued, a payment cleared, a subscription status changed. They do not record what MRR's definition requires, which is a classification of each of those events into the right bucket, normalized to a monthly cadence. Every pitfall that follows lives in that gap between what the table stores and what the definition demands.
Pitfall 1: Counting an annual contract at full value instead of dividing it by twelve
The single most damaging error in MRR calculation is treating an annual contract as if it were a monthly one, recording the full invoiced amount in the month it was paid rather than spreading it across the twelve months it covers. The effect is a visible spike in the month of payment followed by a cliff in every month after, even though nothing about the underlying business changed.
Take the canonical example of a customer who pays $60 in January to cover the full year. A query that sums raw invoice amounts puts $60 into January's MRR and nothing into the eleven months that follow. The correct contribution is a twelfth of that amount, five dollars, added to every month the contract covers. The same logic holds for quarterly and semi-annual plans: divide by 3 and by 6, respectively. Any query that does not branch on the plan's interval length will either overstate or understate MRR for every subscriber on a non-monthly billing cycle.
The trouble is that a Postgres billing table makes an annual invoice look almost identical to a monthly one. The columns are the same; only the amount and the interval field differ. A plain SUM on the amount column, with no CASE statement or NULLIF check against the interval, will treat every row as equivalent regardless of what period it actually covers. Getting this right starts with branching the query on interval type before aggregation, not after. The more structural fix, for tracking which subscriptions were actually active across a stretch of time rather than just normalizing the dollar amount of a single payment, belongs to the next pitfall.
Pitfall 2: Joining on invoice timestamps instead of subscription period, so cancelled and not-yet-started subscriptions contaminate the count
Where Pitfall 1 is a math error on the right row, this one is a row-selection error: the query pulls in rows that should never have been counted. Joining an MRR query on invoice timestamps tells you when cash arrived. It tells you nothing about whether a subscription was actually active during the period being measured. A subscription that invoiced in March and then canceled in April will still count as active revenue for months it never delivered, while a subscription whose first invoice falls just outside the query window is excluded even though it was live for part of the period.
Crunchy Data's writeup on projecting monthly revenue run rate in Postgres lays out the fix for exactly this problem, built around Postgres's native range types. In an on-demand subscription model, where customers can start and stop service at any point, MRR can shift on any given day, so the subscription's duration gets stored as a tstzrange on the period column, and the query uses the overlap operator (&&) to test whether a subscription was active on a specific date. In plain terms: instead of asking "was there an invoice this month," the query asks "does this subscription's active window overlap with this day," which is a fundamentally different and more accurate question.
Mechanically, the query builds a daily range using tstzrange(dates.day, dates.day + '[1 day'](https://www.crunchydata.com/blog/postgres-queries-for-projecting-monthly-revenue-run-rate)::interval) and checks it against subscription.period. Where the two overlap, the subscription's rate counts for that day; where they don't, it contributes zero. A LATERAL join against generate_series produces the sequence of dates that drives this comparison, letting the query check every subscription against every day in the window regardless of when any particular invoice happened to post.
Teams whose schema stores start and end dates as separate timestamp columns, rather than a native range type, can still apply the same overlap logic by constructing the range inline. The catch is that the result is only as reliable as the end-date column. A NULL end date, left unpopulated when a subscription is canceled, is a common data-quality gap, and it makes a canceled subscription look like it's still active indefinitely. Invoice date is not the same thing as subscription state, and any query treating the two as interchangeable will misstate which customers were actually paying on any given day.
Pitfall 3: Including free trials in the active subscription count
This pitfall is a classification error: the row exists, it's in the right time window, but the query should have excluded it from revenue from the start. Billing systems typically record a trial as a subscription with a status of "trialing," and a query that filters only on whether a subscription row exists, rather than on its payment status, pulls those trial rows straight into the total.
A subscription existing in the table is not the same as recurring revenue being committed, and a naive COUNT or SUM has no way to tell the difference unless the query explicitly filters status. For a founder watching the dashboard, an active-subscription count inflated by trial users will make the business look healthier than it is. The correction arrives suddenly and badly: when a trial cohort converts or churns, MRR appears to collapse or spike even though nothing changed in actual paid demand. The fix is a status filter at the query level.
Pitfall 4: Mixing booked MRR and billed MRR in the same query
Before a customer has actually started paying, the revenue associated with their subscription is booked. Booked MRR is a legitimate and useful input for sales forecasting. Presenting it as current MRR to investors or a board overstates the revenue base the business can actually point to today. The fix is a status filter restricting the query to rows where payment has been collected or is actively due under a subscription already in force.
This mixing occurs often in teams whose CRM or sales tooling pre-creates subscription records the moment a deal closes verbally, well before any invoice has gone out. Those rows are real rows sitting in the production database, tied to real deals, and a query without a status guard will happily sum them alongside revenue that's actually been billed. Billing tables often hold future-looking records side by side with current ones, and SQL distinguishes between them only when the query includes an explicit filter.
Pitfall 5: Misclassifying reactivation MRR as new business MRR
This is the subtlest of the six pitfalls, because the row in question does belong in MRR. It belongs in a specific bucket, and this pitfall is about identifying which one. A customer who churned six months ago and just resubscribed produces a row in the billing table that looks, on its face, identical to a brand-new customer's first invoice. Without a join back to that customer's churn history, a query that classifies every first-invoice-in-period as New Business MRR will overstate how much new-customer acquisition is actually happening.
Reactivation MRR, revenue from a previously churned customer returning to a paid plan, is one of the six defined MRR movements, and it is a distinct category from New Business MRR. Definitions of these categories can vary somewhat from one company to another, but the discipline that matters is making sure every relevant change gets captured somewhere and that the common pitfalls don't quietly erode the number. That means the team decides where reactivations land inside the SQL itself, not after the fact in a dashboard filter.
The data to make this distinction correctly already exists in most billing schemas; it just takes a slightly more involved query to use it. The fix requires a subquery or CTE identifying customer IDs that previously held an active subscription, followed by a LEFT JOIN or NOT EXISTS check that flags each current-period subscription as either genuinely new or a reactivation before anything gets counted. Skipping this step has a real cost for anyone reading the numbers: if reactivations are material and get folded into New Business MRR, churn looks lower than it actually was, since those customers did churn before coming back, and new-customer acquisition efficiency looks better than it actually is. Both distortions mislead an investor comparing cohort performance across periods.
Pitfall 6: Excluding discounts from the MRR calculation
This is the most arithmetically simple of the six pitfalls, but it compounds quietly across a customer base because it's easy to forget the discount table exists. A query that sums the list price of every active subscription, without subtracting whatever discount applies, overstates MRR by the full discount amount for every discounted customer in the base.
In the canonical example, Customer D was offered a discount and pays $8 a month rather than list price. MRR has to reflect the $8 actually paid. Discounts need to be calculated separately and then subtracted, covering both a straightforward monthly discount and a discount applied specifically for paying an annual contract upfront.
The reason this gets missed is that discount data is usually stored in a structurally separate place from the subscription amount. In billing data loaded into Postgres, discounts typically live in a separate coupons table, joined through a discount_coupon_id field, carrying columns like percent_off or amount_off. A SUM run only against the subscription amount column will never see that table. Getting the number right means joining to the discount record and applying it before any aggregation happens, not treating it as a correction to run afterward.
Why These Errors Persist
Every pitfall above shares one trait: the query that produces it executes without error and returns a number that looks entirely plausible on a dashboard. Postgres gives no runtime signal that an annual contract went uncounted, that a trial got summed in, or that a discount table never got joined. These errors persist precisely because nothing forces a reconciliation against the underlying contracts, invoices, and accounting records that would expose them. MRR is only trustworthy when it ties back to those records; a dashboard number that climbs steadily month over month can still be wrong if cancellations, one-time fees, or contract amendments were classified incorrectly somewhere upstream.
Founders and operators writing these queries themselves, without a data team reviewing the logic line by line, carry the most exposure. The query gets written once, saved into a notebook or wired into a dashboard, and then trusted indefinitely, while the billing schema underneath it changes shape over time. The semantic gap between what a query returns and what the business actually means by MRR is why so many teams end up with numbers they can't trust, and why pre-calculated, governed metrics matter more than another round of self-service SQL. Dreambase pre-models MRR as a governed dataset specifically so that teams, and the AI agents increasingly writing queries on their behalf, work from one auditable definition instead of rebuilding the same amortization and classification logic from scratch each time someone needs a number.
That risk compounds as AI agents start generating MRR queries directly against raw billing tables. An agent reproduces the same normalization mistakes a human analyst would make, annual contracts counted at full value, trials folded into active counts, discounts left unjoined, and it does so faster, at greater scale, and without flagging any of the semantic assumptions baked into the query it just wrote. Establishing one organization-wide definition of MRR, which rows count, which intervals get normalized, which revenue types get excluded, is semantic work that most teams end up solving ad hoc, and the usual result is conflicting numbers depending on who ran the query. Dreambase bakes that definition into a pre-modeled dataset so that every stakeholder, human or automated, reads from the same source of truth rather than reconstructing the logic independently. The structural fix is a definition of MRR that's settled once, enforced consistently, and never left to be rediscovered by whoever happens to be writing SQL that week.