How to calculate deferred revenue in SQL
Contents:
What deferred revenue is and why finance cares
Deferred revenue is the dollar amount a SaaS business has already collected but has not yet earned. A customer wires 1,200 USD on January 1 for a twelve-month subscription, but on January 1 the company has delivered exactly zero days of service. The whole 1,200 USD sits on the balance sheet as a liability — a promise to deliver software for the next 365 days — and converts into revenue one slice at a time as service days pass. By June 30, half of the contract has been delivered and 600 USD has rotated into recognized revenue; the remaining 600 USD is still deferred.
The reason this matters in interviews is that ARR, MRR, bookings, cash, and deferred revenue all describe the same subscription book from different angles, and a senior analyst at Snowflake, Stripe, or Databricks is expected to keep them straight without looking up the definition. Finance leans on deferred revenue at every quarter close because it is the audit-grade ground truth: the liability balance is reconciled against the cash that came in and the revenue that was recognized, and any drift between the three is a red flag that someone is booking the wrong number into the income statement.
This post walks through the SQL you would actually run: the textbook prorate per active subscription, the monthly amortization schedule, and the balance sheet snapshot at an arbitrary date. Every query is Postgres and translates with minor edits to Snowflake, BigQuery, or Redshift.
The formula and the mental model
The textbook definition is one line.
deferred_revenue(t) = total_prepayment - revenue_recognized_through(t)Recognized revenue grows linearly from zero on the contract start date to the full prepayment on the contract end date. Deferred revenue does the mirror: it starts at the full prepayment and decays to zero. At any point t between the two endpoints, the split is just the fraction of the service period that has elapsed.
revenue_recognized(t) = total_prepayment × min(1, max(0, (t - start) / (end - start)))
deferred_revenue(t) = total_prepayment − revenue_recognized(t)The min/max clamp keeps the math sane outside the service window — before the start date nothing is recognized, after the end date everything is, and you never end up recognizing more than you billed. Skipping that clamp is the most common bug, and an interviewer will plant a row with period_end < CURRENT_DATE specifically to see whether the candidate notices.
The data model we will use
Assume two tables.
subscriptions (subscription_id, customer_id, plan_amount,
billed_at, period_start, period_end, status)
billing_events (subscription_id, event_at, kind, amount)subscriptions.plan_amount is the total prepayment in USD for the full service period — 1,200 USD for an annual contract billed up front, 100 USD for a monthly contract. period_start and period_end bracket the service window the customer is paying for. status is one of active, cancelled, expired, or paused. billing_events.kind is charge, refund, chargeback, or credit.
Three SQL queries you will actually ship
1. Per-subscription deferred at today
The simplest version: for every active subscription, prorate by elapsed days and subtract.
WITH subs AS (
SELECT
subscription_id,
customer_id,
plan_amount,
period_start,
period_end,
(period_end - period_start)::NUMERIC AS total_days,
GREATEST(0,
LEAST(CURRENT_DATE, period_end) - period_start
)::NUMERIC AS days_elapsed
FROM subscriptions
WHERE status = 'active'
)
SELECT
subscription_id,
customer_id,
plan_amount,
LEAST(plan_amount,
plan_amount * days_elapsed / NULLIF(total_days, 0)
)::NUMERIC AS recognized_revenue,
plan_amount - LEAST(plan_amount,
plan_amount * days_elapsed / NULLIF(total_days, 0)
)::NUMERIC AS deferred_revenue
FROM subs;Three things to call out. The LEAST(CURRENT_DATE, period_end) clamp prevents over-recognition on subscriptions whose end date has passed but whose status has not yet flipped to expired. The GREATEST(0, ...) clamp handles future-dated contracts where the service window has not begun. And NULLIF(total_days, 0) is the defensive guard against a malformed row where period_start = period_end, which would otherwise crash the query with a division-by-zero error during a quarter close.
2. Monthly amortization schedule
The query finance actually wants is the schedule: for each month in the period, how much revenue was recognized? This is the working paper that ties the recognized-revenue line on the income statement back to the cash that came in.
WITH months AS (
SELECT generate_series(
'2026-01-01'::DATE,
'2026-12-01'::DATE,
INTERVAL '1 month'
)::DATE AS month_start
),
slices AS (
SELECT
s.subscription_id,
s.plan_amount,
m.month_start,
GREATEST(s.period_start, m.month_start) AS slice_start,
LEAST(s.period_end,
(m.month_start + INTERVAL '1 month')::DATE) AS slice_end,
(s.period_end - s.period_start)::NUMERIC AS total_days
FROM subscriptions s
CROSS JOIN months m
WHERE s.status IN ('active', 'expired')
AND s.period_start < (m.month_start + INTERVAL '1 month')::DATE
AND s.period_end > m.month_start
)
SELECT
month_start,
SUM(
plan_amount
* GREATEST(0, slice_end - slice_start)::NUMERIC
/ NULLIF(total_days, 0)
)::NUMERIC AS recognized_revenue
FROM slices
GROUP BY month_start
ORDER BY month_start;The trick is the CROSS JOIN with month-bucket filters: every subscription gets paired with every month it overlaps, and the per-slice day count comes out of slice_end - slice_start. Summing that across subscriptions inside a month gives the recognized revenue for that month. The same pattern shows up in the cohort revenue post, where the slicing is by cohort instead of by calendar month, and it is worth internalizing because it generalizes to any time-bucketed amortization.
3. Balance sheet snapshot at an arbitrary date
Auditors do not ask for "deferred revenue today" — they ask for "deferred revenue as of March 31, 2026, the close of Q1." Parameterize the snapshot date and the query becomes time-travel-safe.
WITH params AS (SELECT '2026-03-31'::DATE AS as_of),
active AS (
SELECT
s.plan_amount,
s.period_start,
s.period_end,
(s.period_end - s.period_start)::NUMERIC AS total_days,
GREATEST(0,
LEAST(p.as_of, s.period_end) - s.period_start
)::NUMERIC AS days_elapsed
FROM subscriptions s
CROSS JOIN params p
WHERE s.billed_at <= p.as_of
AND s.period_end > p.as_of
)
SELECT
SUM(plan_amount) AS total_billed,
SUM(LEAST(plan_amount,
plan_amount * days_elapsed
/ NULLIF(total_days, 0))) AS recognized_so_far,
SUM(plan_amount)
- SUM(LEAST(plan_amount,
plan_amount * days_elapsed
/ NULLIF(total_days, 0))) AS deferred_revenue
FROM active;The filter is billed_at <= as_of AND period_end > as_of, not status = 'active'. The status column reflects the world today, not the world on the snapshot date. A subscription that was active on March 31 and cancelled on April 12 should still appear in the Q1 snapshot. Filtering on status instead of on the date range is the classic mistake that makes the snapshot drift over time as the operational table mutates.
Common pitfalls
The first trap is missing the clamps. Writing the prorate as plan_amount * (CURRENT_DATE - period_start) / (period_end - period_start) without LEAST and GREATEST produces negative recognized revenue on future-dated subscriptions and over-recognition on expired ones. The fix is to wrap the elapsed-days fraction in GREATEST(0, ...) on the bottom and LEAST(plan_amount, ...) on the top, every single time. The query in section one is verbose precisely because it does both clamps explicitly.
The second trap is treating non-recurring fees as part of the deferred-revenue pool. Setup fees, onboarding, and one-off services have their own recognition policies and frequently do not amortize on the subscription curve. Mixing them into the main query inflates deferred revenue early in a contract and crashes it later when the lump-sum recognition fires. The fix is to model subscriptions and non_recurring_charges as separate streams, deferred and recognized under their own rules, and combine only at the balance-sheet roll-up.
The third trap is cancellation-and-refund accounting. When a customer cancels mid-period, the deferred balance is not "the rest of the year" — it is "the rest of the year minus whatever refund the company issues." The same logic applies to chargebacks, where the bank reverses the cash and the deferred balance has to follow. The cleaner architectural fix is to maintain a recognized_revenue_events log alongside the subscription table so every change is auditable.
The fourth trap is mixing cash-basis and accrual-basis logic. Cash-basis recognizes the whole 1,200 USD on the day the wire lands. Accrual-basis recognizes one twelfth per month and parks the rest as deferred. Public SaaS companies are accrual; many startups carry cash-basis numbers in finance and accrual-basis numbers in analytics, and the two reports disagree until somebody traces the discrepancy. The fix is to write every query against the accrual rules and document the basis at the top of the query.
The fifth trap is reconciliation drift. The deferred-revenue balance has to satisfy the identity opening + billed - recognized - refunded = closing at every period boundary. If the rollforward does not tie out, the bug is upstream — a billing event misdated, a refund posted to the wrong period, a cancellation effective date inconsistent between the operational and analytical sources. Always reconcile before publishing the snapshot.
Optimization tips
Most deferred-revenue queries are cheap because the active subscription set is small. The expensive one is the monthly amortization, which CROSS JOINs every active subscription against every month in the window — a hundred thousand subscriptions against twenty-four months is 2.4 million rows before any aggregation. The first lever is to bound the months tightly: a quarter close needs three months, not twenty-four.
The second lever is partitioning the billing_events table by event_at. Monthly partitions are conventional. Every snapshot query is naturally bounded by a date range, and partition pruning collapses what looks like a full-table scan into the two months actually touched. On Snowflake, cluster on the date columns; on BigQuery, partition plus cluster on subscription_id.
The third lever is materializing the recognized-revenue-per-subscription-per-month slice as a daily_revenue_recognition table. Every dashboard reads from it, the inputs only change when new subscriptions or cancellations land, and the cost of recomputing the slice on every dashboard load adds up fast in a busy quarter. A nightly refresh keeps the audit working papers and the executive deck in sync without re-running the CROSS JOIN.
Related reading
- How to calculate ARR in SQL — the recurring-revenue projection that sits next to deferred on every SaaS finance dashboard.
- How to calculate contraction MRR in SQL — the downgrade side of the recognition story.
- How to calculate cohort revenue in SQL — the same time-slicing pattern applied to acquisition cohorts.
- How to calculate cash conversion cycle in SQL — the receivables-and-cash counterpart to the liability view.
If you want to drill SQL questions like this every day, NAILDD is launching with hundreds of analytics SQL problems built exactly around this pattern — recurring revenue, recognition, balance sheets, and reconciliation.
FAQ
Is deferred revenue a liability or an asset?
Deferred revenue is a liability. The intuition is that the company owes the customer something — specifically, the rest of the service period the customer paid for. Until that service is delivered, the cash is contractually obligated, not earned. On the balance sheet it sits under current liabilities if the service period ends within twelve months and long-term liabilities if it stretches further out. The moment service is delivered, the matching slice rotates out of the liability bucket and into recognized revenue on the income statement.
How does deferred revenue interact with ARR and MRR?
ARR and MRR are run-rate projections of the current subscription book — they answer "what is the company on track to bill over the next year?" Deferred revenue is the unearned portion of cash already collected — it answers "how much of what we have already been paid have we not yet delivered?" The two are mechanically related — a SaaS company with all-annual contracts has a high deferred balance and an ARR roughly proportional to it — but they are not the same number, and they tell different stories.
How does a refund affect deferred revenue?
A refund reduces deferred revenue by the refund amount and reduces cash by the same amount; recognized revenue is unaffected. The intuition: the refund is paying back service the company never delivered, so it comes out of the liability bucket, not the revenue bucket. If the refund exceeds the remaining deferred balance — for instance, a customer demands a full refund six months into an annual contract — the excess hits recognized revenue as a contra-revenue entry, and the rollforward picks up a "refund of recognized revenue" line.
Why does deferred revenue matter for audit and SOX compliance?
Deferred revenue is one of the most consequential single accounts on a SaaS balance sheet, and it is also one of the easiest to manipulate — pulling a few days of recognition forward inflates revenue and deflates the liability, and the manipulation is invisible without per-subscription working papers. Auditors test the deferred balance every quarter by reperforming the recognition calculation on a sample of contracts and tying the rollforward to cash and revenue. If the four numbers in the rollforward do not reconcile, the analyst has to find the bug before the auditor does.