Revenue Recognition and Deferred Revenue

Revenue recognition is one of the most consequential accounting decisions a business makes — it determines when income appears on the P&L, and therefore when investors, lenders, and tax authorities see a healthy or struggling business. Get it wrong and you either inflate current profits (reporting revenue before you've earned it) or deflate them (recording cash that's already been received as a liability). Both distort the picture.

This lesson covers the three main recognition methods and how to query each one, plus backlog calculation, MRR tracking, and a revenue forecast model — the queries that feed finance dashboards, board reports, and fundraising due diligence.

Cash vs Accrual vs Deferred

Before writing SQL, it's worth being precise about what each method means, because they can produce dramatically different numbers for the same underlying business activity:

The key tension: a client who pays a £120,000 annual retainer in January has given you the cash, but you've only earned £10,000/month. Cash basis shows £120k in January; deferred (accrual) shows £10k/month for 12 months. The difference is massive on a monthly P&L.

Example 1: Accrual vs Cash Revenue Comparison

This query calculates both accrual revenue (based on invoice issue date — when the service was billed) and cash revenue (based on payment date — when money was actually received) for every calendar month, then places them side by side.

The reason to compare them together is to identify the timing gap: months where you've billed heavily but collected little (growing AR risk), and months where you collected cash from prior-period invoices (which inflates cash revenue that month). A widening gap is a warning sign.

The FULL OUTER JOIN is essential here — there may be months where invoices were issued but nothing paid yet (accrual > 0, cash = 0), or months where old invoices get paid with no new invoicing (cash > 0, accrual = 0). A regular LEFT JOIN or INNER JOIN would lose those months entirely.

WITH monthly_accrual AS (
    SELECT DATE_TRUNC('month', i.issue_date)::DATE AS month,
        ROUND(SUM(ii.quantity * ii.unit_price), 2) AS accrual_revenue
    FROM invoices i JOIN invoice_items ii ON ii.invoice_id = i.invoice_id
    GROUP BY 1
),
monthly_cash AS (
    SELECT DATE_TRUNC('month', payment_date)::DATE AS month,
        ROUND(SUM(amount), 2) AS cash_revenue
    FROM payments GROUP BY 1
)
SELECT COALESCE(a.month, c.month) AS month,
    COALESCE(a.accrual_revenue, 0) AS accrual_basis,
    COALESCE(c.cash_revenue, 0) AS cash_basis,
    COALESCE(a.accrual_revenue, 0) - COALESCE(c.cash_revenue, 0) AS accrual_vs_cash_diff
FROM monthly_accrual a
FULL OUTER JOIN monthly_cash c ON c.month = a.month
ORDER BY 1;

What This Returns

A healthy business shows a small, stable difference. A growing positive gap means clients are slow to pay. A large negative gap in one month often indicates a year-end or quarter-end collection push.

Example 2: Revenue Backlog

The revenue backlog is the total value of invoices that have been issued (the service or product is contracted) but the due date has not yet arrived — meaning the revenue is committed but not yet recognised. This is a forward-looking health metric: it shows how much revenue is already "locked in" for future periods without any new sales needed.

This query groups future-due invoices by month, then adds a cumulative running total. The running total answers the question: "If no new invoices are created, how much revenue will we see over the next N months?"

-- Invoices issued but not yet due (future revenue)
WITH future_revenue AS (
    SELECT
        DATE_TRUNC('month', i.due_date)::DATE AS due_month,
        COUNT(DISTINCT i.invoice_id) AS invoices,
        ROUND(SUM(ii.quantity * ii.unit_price), 2) AS backlog
    FROM invoices i
    JOIN invoice_items ii ON ii.invoice_id = i.invoice_id
    WHERE i.due_date > CURRENT_DATE
    GROUP BY 1
)
SELECT due_month, invoices, backlog,
    SUM(backlog) OVER (ORDER BY due_month) AS cumulative_backlog
FROM future_revenue ORDER BY due_month;

What This Returns

The SUM(...) OVER (ORDER BY due_month) is a running-sum window function. It doesn't group rows — it keeps each month as its own row while adding a cumulative column. This is much more useful than just summing the whole table, because it lets you see the shape of the revenue pipeline month by month.

Example 3: Monthly Recurring Revenue Tracking

For businesses with a subscription or retainer model, MRR (Monthly Recurring Revenue) is the single most important metric. Unlike one-time revenue, MRR is predictable — it's the annuity component of the business. Investors and acquirers often value subscription businesses as a multiple of MRR rather than annual revenue.

This query builds an MRR table that shows not just the total, but also how many clients are new this month (expansion) versus how many were active last month but not this month (churn). The difference between new and churned gives net growth — the clearest signal of whether the subscriber base is growing or contracting.

The subquery pattern NOT EXISTS (SELECT 1 FROM ... WHERE mc2.month < monthly_clients.month) is the standard way to identify first-time appearances: a client is "new" in month X if there is no record of them in any earlier month.

WITH monthly_clients AS (
    SELECT client_id,
        DATE_TRUNC('month', issue_date)::DATE AS month,
        SUM(ii.quantity * ii.unit_price) AS monthly_rev
    FROM invoices i JOIN invoice_items ii ON ii.invoice_id = i.invoice_id
    GROUP BY client_id, 2
    HAVING COUNT(DISTINCT i.invoice_id) >= 1
),
mrr AS (
    SELECT month,
        COUNT(DISTINCT client_id) AS active_clients,
        SUM(monthly_rev) AS mrr,
        -- New clients this month
        COUNT(DISTINCT client_id) FILTER (
            WHERE NOT EXISTS (
                SELECT 1 FROM monthly_clients mc2
                WHERE mc2.client_id = monthly_clients.client_id
                  AND mc2.month < monthly_clients.month
            )
        ) AS new_clients,
        -- Churned from last month
        (SELECT COUNT(DISTINCT mc_prev.client_id) FROM monthly_clients mc_prev
         WHERE mc_prev.month = monthly_clients.month - INTERVAL '1 month'
           AND mc_prev.client_id NOT IN (
               SELECT client_id FROM monthly_clients mc_curr
               WHERE mc_curr.month = monthly_clients.month
           )) AS churned_clients
    FROM monthly_clients
    GROUP BY month
)
SELECT month, active_clients, ROUND(mrr, 2) AS mrr,
    new_clients, churned_clients,
    ROUND(100.0 * (active_clients - churned_clients + new_clients - active_clients)
        / NULLIF(active_clients, 0), 1) AS net_growth_pct
FROM mrr ORDER BY month;

What This Returns

A healthy SaaS business targets a churn rate below 2% monthly (roughly 22% annually). If net_growth_pct is consistently positive, the business is expanding even without counting upsells.

Example 4: Days of Backlog Coverage

Days of Coverage answers a simple but powerful question: if no new invoices are created from today onwards, how many days of business operations are "covered" by the existing outstanding AR? This is a liquidity and revenue visibility metric.

The calculation works by dividing total outstanding AR (invoiced but unpaid) by the daily revenue run rate (total revenue over the past year divided by 365). The result is the number of days' worth of revenue sitting in the AR pipeline.

WITH daily_run_rate AS (
    SELECT SUM(ii.quantity * ii.unit_price) / 365.0 AS daily_revenue
    FROM invoices i JOIN invoice_items ii ON ii.invoice_id = i.invoice_id
    WHERE i.issue_date >= CURRENT_DATE - INTERVAL '1 year'
),
outstanding_ar AS (
    SELECT SUM(ii.quantity * ii.unit_price) - COALESCE(SUM(p.amount), 0) AS total_ar
    FROM invoices i JOIN invoice_items ii ON ii.invoice_id = i.invoice_id
    LEFT JOIN payments p ON p.invoice_id = i.invoice_id
    WHERE ii.quantity * ii.unit_price > COALESCE(p.amount, 0)
)
SELECT
    ROUND(oa.total_ar, 2) AS outstanding_ar,
    ROUND(drr.daily_revenue, 2) AS daily_revenue_run_rate,
    ROUND(oa.total_ar / NULLIF(drr.daily_revenue, 0), 0) AS days_of_coverage
FROM outstanding_ar oa, daily_run_rate drr;

What This Returns

Interpretation benchmarks:

• < 30 days: Thin pipeline, highly dependent on new sales
• 30–90 days: Healthy for most B2B service businesses
• 90–180 days: Strong visibility; possibly some very long payment terms
• > 180 days: Check for stale AR — old invoices that may never be collected are inflating this number