Как посчитать Deferred Revenue в SQL

Зачем Deferred Revenue

В SaaS юзер платит $1200 в начале года. Revenue recognised не сразу — пропорционально по месяцам ($100/мес). Deferred Revenue = $1100 в начале second month — обязательство ещё «отдать» сервис.

Формула

Deferred Revenue at time t = total_prepayment - revenue_recognized_so_far

Базовый расчёт

Данные: subscriptions(user_id, plan_amount, billed_at, period_start, period_end).

WITH subs AS (
    SELECT
        user_id,
        plan_amount,
        period_start,
        period_end,
        (period_end - period_start)::NUMERIC AS total_days,
        CURRENT_DATE AS as_of
    FROM subscriptions
    WHERE status = 'active'
)
SELECT
    user_id,
    plan_amount,
    -- Recognized revenue: days passed / total days × plan
    LEAST(plan_amount,
        plan_amount * GREATEST(0, EXTRACT(EPOCH FROM (LEAST(CURRENT_DATE, period_end) - period_start)) / 86400)::NUMERIC
        / NULLIF(total_days, 0)
    ) AS revenue_recognized,
    -- Deferred: total - recognized
    plan_amount - LEAST(plan_amount,
        plan_amount * GREATEST(0, EXTRACT(EPOCH FROM (LEAST(CURRENT_DATE, period_end) - period_start)) / 86400)::NUMERIC
        / NULLIF(total_days, 0)
    ) AS deferred_revenue
FROM subs;

Monthly amortization

WITH monthly_recognized AS (
    SELECT
        s.user_id,
        s.plan_amount,
        d.month,
        -- Days в месяце, попадающих в subscription period
        GREATEST(0, EXTRACT(EPOCH FROM (
            LEAST(s.period_end, d.month + INTERVAL '1 month')
            - GREATEST(s.period_start, d.month)
        )) / 86400) AS days_in_month
    FROM subscriptions s
    CROSS JOIN (
        SELECT generate_series('2026-01-01'::DATE, '2026-12-01'::DATE, INTERVAL '1 month')::DATE AS month
    ) d
    WHERE s.status = 'active'
)
SELECT
    month,
    SUM(plan_amount * days_in_month / NULLIF((period_end - period_start)::NUMERIC, 0)) AS recognized_revenue
FROM monthly_recognized m
JOIN subscriptions s ON s.user_id = m.user_id
GROUP BY month
ORDER BY month;

Balance sheet

Total deferred at snapshot:

WITH all_subs AS (
    SELECT
        plan_amount,
        period_start,
        period_end,
        GREATEST(0, EXTRACT(EPOCH FROM (CURRENT_DATE - period_start)) / 86400) AS days_passed,
        (period_end - period_start)::NUMERIC AS total_days
    FROM subscriptions
    WHERE status = 'active'
      AND period_end > CURRENT_DATE
)
SELECT
    SUM(plan_amount) AS total_billed,
    SUM(LEAST(plan_amount, plan_amount * days_passed / NULLIF(total_days, 0))) AS recognized_so_far,
    SUM(plan_amount) - SUM(LEAST(plan_amount, plan_amount * days_passed / NULLIF(total_days, 0))) AS deferred_revenue
FROM all_subs;

Частые ошибки

Ошибка 1. Не учитывать non-recurring fees. Setup fees, onboarding — другая recognition. Иногда immediately, иногда over period.

Ошибка 2. Cancellation mid-period. Юзер отменил mid-month. Recognize prorate, refund остаток.

Ошибка 3. Upgrade mid-period. Тариф changed. Adjust recognition.

Ошибка 4. Subscription vs Usage-based. Subscription — pre-paid (deferred). Usage — pay-as-you-go (no deferred).

Ошибка 5. Cash vs accrual. Cash basis — recognise при payment. Accrual — over period. Большинство SaaS — accrual.

Связанные темы

• Как посчитать MRR в SQL
• Как посчитать ARR в SQL
• Как посчитать revenue в SQL
• Как посчитать NRR в SQL

FAQ

Deferred Revenue — это liability?

Да. На balance sheet — обязательство «отдать сервис».

Как уменьшается Deferred Revenue?

Каждый месяц recognising revenue (move from liability to revenue).

Annual vs Monthly subscriptions?

Annual создаёт большой deferred. Monthly — почти нет.

Refund — что делать с Deferred?

Reduce deferred + adjust cash. Recognised revenue stay.

Audit важность?

В public companies — критично. SOX compliance.