SQL для subscription-бизнеса

Зачем это знать

Подписочная модель — один из самых быстрорастущих сегментов: на ней работают Карьерник, Skyeng, Okko, Ivi, Кинопоиск, Яндекс.Плюс и десятки B2B-SaaS. Везде, где выручка приходит не разовыми покупками, а регулярными списаниями, аналитик обязан уметь считать метрики подписки — и почти всегда делает это на SQL.

У подписочного бизнеса свой набор метрик: MRR, ARR, churn, NRR, когортный retention, expansion. Их нельзя корректно посчитать «на глаз» или простым SUM — почти каждая требует аккуратной работы с датами начала и конца подписки, состоянием на определённый момент и когортами. Поэтому на собесах в SaaS-компаниях эти запросы спрашивают постоянно.

Схема данных

Дальше во всех примерах используется упрощённая схема из трёх таблиц:

-- subscriptions: одна строка на подписку
(id, user_id, plan, amount_monthly, started_at, ended_at, status)

-- payments: фактические списания
(id, subscription_id, paid_at, amount)

-- users: пользователи
(id, signup_date, plan)

MRR (Monthly Recurring Revenue)

MRR — это регулярная выручка, приведённая к месяцу. Текущий MRR — сумма месячных платежей по всем активным подпискам:

SELECT SUM(amount_monthly) AS mrr
FROM subscriptions
WHERE status = 'active';

Чаще нужен MRR по месяцам, чтобы видеть динамику. Здесь важна логика: подписка вносит вклад в MRR того месяца, если она началась до этого месяца и ещё не закончилась к нему:

WITH months AS (
    SELECT generate_series(
        '2026-01-01'::DATE,
        CURRENT_DATE,
        '1 month'
    ) AS month
)
SELECT
    m.month,
    SUM(CASE
        WHEN s.started_at <= m.month
         AND (s.ended_at IS NULL OR s.ended_at > m.month)
        THEN s.amount_monthly
        ELSE 0
    END) AS mrr
FROM months m
LEFT JOIN subscriptions s ON TRUE
GROUP BY m.month
ORDER BY m.month;

ARR (Annual Recurring Revenue)

ARR — та же регулярная выручка, но в годовом выражении: ARR = MRR × 12. Это не фактическая выручка за прошлый год, а прогноз на год вперёд при текущем уровне подписок.

Churn rate

Отток (churn) — доля клиентов или выручки, которую бизнес потерял за период. Помесячный отток по числу подписок считается как отменённые за месяц, делённые на активную базу на начало месяца:

WITH monthly AS (
    SELECT
        DATE_TRUNC('month', ended_at) AS churn_month,
        COUNT(*) AS churned
    FROM subscriptions
    WHERE status = 'cancelled'
    GROUP BY 1
),
active_start AS (
    SELECT
        DATE_TRUNC('month', ended_at) AS month,
        COUNT(*) AS active_beginning_of_month
    FROM subscriptions
    WHERE started_at < month
    GROUP BY 1
)
SELECT
    m.churn_month,
    m.churned * 1.0 / a.active_beginning_of_month AS churn_rate
FROM monthly m
JOIN active_start a ON m.churn_month = a.month;

Ориентир для здорового SaaS — меньше 2% оттока подписок в месяц. Важно различать churn по числу клиентов и по выручке (revenue churn): один ушедший крупный клиент бьёт по выручке сильнее, чем несколько мелких.

Net Revenue Retention (NRR)

NRR показывает, что произошло с выручкой от одной и той же когорты клиентов за период — с учётом того, что кто-то доплатил (expansion), кто-то ушёл (churn), а кто-то понизил тариф (downgrade):

NRR = (Начальный MRR + expansion − churn − downgrade) / Начальный MRR

WITH base AS (
    SELECT
        user_id,
        SUM(CASE WHEN period = 'start' THEN mrr END) AS start_mrr,
        SUM(CASE WHEN period = 'END' THEN mrr END) AS end_mrr
    FROM user_mrr
    WHERE user_id IN (SELECT user_id FROM subscriptions WHERE active_at_period_start)
    GROUP BY user_id
)
SELECT
    SUM(end_mrr) / SUM(start_mrr) AS nrr
FROM base;

NRR выше 100% — заветная цель: это значит, что доплаты существующих клиентов перекрывают потери от оттока, и бизнес растёт даже без привлечения новых.

Когортный retention для подписки

Retention по когортам показывает, какая доля подписчиков, стартовавших в один месяц, продолжает платить через 1, 2, 3 месяца. Сначала определяем когорту по месяцу старта, затем считаем, в каких месяцах эти пользователи платили:

WITH cohorts AS (
    SELECT
        user_id,
        DATE_TRUNC('month', started_at) AS cohort_month
    FROM subscriptions
),
active_months AS (
    SELECT
        s.user_id,
        c.cohort_month,
        DATE_TRUNC('month', p.paid_at) AS pay_month
    FROM cohorts c
    JOIN payments p USING (subscription_id)
    JOIN subscriptions s USING (subscription_id)
)
SELECT
    cohort_month,
    EXTRACT(MONTH FROM AGE(pay_month, cohort_month)) +
        EXTRACT(YEAR FROM AGE(pay_month, cohort_month)) * 12 AS months_since,
    COUNT(DISTINCT user_id) AS active
FROM active_months
GROUP BY 1, 2
ORDER BY 1, 2;

Абсолютные числа нормируют на размер когорты — так получают retention в процентах и строят классическую когортную таблицу.

Trial-to-paid

Конверсия из триала в оплату — базовая метрика для подписки с бесплатным периодом. Считаем долю пользователей, которые после триала перешли в платящих:

SELECT
    COUNT(DISTINCT CASE WHEN had_trial THEN user_id END) AS trial_users,
    COUNT(DISTINCT CASE WHEN converted_to_paid THEN user_id END) AS paid_users,
    COUNT(DISTINCT CASE WHEN converted_to_paid THEN user_id END) * 1.0 /
        COUNT(DISTINCT CASE WHEN had_trial THEN user_id END) AS conversion_rate
FROM trial_cohort;

Апгрейды и даунгрейды тарифа

Чтобы отследить переходы между тарифами, сравниваем текущий план подписки с предыдущим у того же пользователя через оконную функцию LAG:

WITH changes AS (
    SELECT
        user_id,
        started_at,
        plan,
        LAG(plan) OVER (PARTITION BY user_id ORDER BY started_at) AS prev_plan
    FROM subscriptions
)
SELECT
    DATE_TRUNC('month', started_at) AS month,
    SUM(CASE WHEN plan > prev_plan THEN 1 ELSE 0 END) AS upgrades,
    SUM(CASE WHEN plan < prev_plan THEN 1 ELSE 0 END) AS downgrades
FROM changes
WHERE prev_plan IS NOT NULL
GROUP BY 1;

Здесь предполагается, что тарифы упорядочены (например, кодируются числом), иначе сравнение plan > prev_plan не имеет смысла — на собесе это хороший повод уточнить схему.

Payback period

Срок окупаемости привлечения клиента — за сколько месяцев выручка от клиента отбивает стоимость его привлечения (CAC):

WITH customer_months AS (
    SELECT
        user_id,
        DATE_DIFF('month', signup_date, CURRENT_DATE) AS tenure_months,
        SUM(amount) AS lifetime_revenue,
        acquisition_cost  -- из маркетинга
    FROM users_data
)
SELECT AVG(
    acquisition_cost / (lifetime_revenue / tenure_months)
) AS avg_payback_months
FROM customer_months
WHERE tenure_months > 0;

Для здорового SaaS payback обычно укладывается в 12 месяцев.

Реактивация

Реактивация — пользователи, которые отменили подписку, а потом вернулись. Их находят по тому, что у пользователя больше одной подписки в истории:

WITH subs_timeline AS (
    SELECT
        user_id,
        started_at,
        ended_at,
        ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY started_at) AS subscription_num
    FROM subscriptions
)
SELECT
    COUNT(DISTINCT user_id) AS reactivated_users
FROM subs_timeline
WHERE subscription_num > 1
    AND started_at BETWEEN '2026-04-01' AND '2026-04-30';

Expansion revenue

Expansion — дополнительная выручка от существующих клиентов (переход на дорогой тариф, докупка мест). Её считают как положительный прирост месячного платежа относительно предыдущего:

WITH mrr_delta AS (
    SELECT
        user_id,
        DATE_TRUNC('month', started_at) AS month,
        amount_monthly,
        LAG(amount_monthly) OVER (PARTITION BY user_id ORDER BY started_at) AS prev_mrr
    FROM subscriptions
)
SELECT
    SUM(GREATEST(amount_monthly - prev_mrr, 0)) AS expansion_mrr
FROM mrr_delta;

GREATEST(..., 0) отсекает даунгрейды — expansion учитывает только рост платежа, снижение сюда не попадает.

Customer health score

Health score объединяет несколько сигналов в один индикатор «здоровья» клиента, чтобы команда поддержки могла заранее увидеть риск оттока:

SELECT
    user_id,
    CASE
        WHEN days_since_last_login > 30 THEN 'Red'
        WHEN features_used < 3 THEN 'Yellow'
        WHEN monthly_payments_stable THEN 'Green'
    END AS health
FROM user_metrics;

Красных клиентов Customer Success-команда берёт в проактивную работу — пишет, звонит, предлагает помощь, пока клиент не ушёл.

Ключевые SaaS-коэффициенты

CAC payback

Стоимость привлечения (CAC), делённая на месячную выручку с клиента. Показывает, за сколько месяцев окупается привлечение. Хороший ориентир — меньше 12 месяцев.

LTV / CAC

Пожизненная ценность клиента (LTV), делённая на стоимость его привлечения (CAC). Показывает, во сколько раз клиент приносит больше, чем стоило его привести. Здоровое значение — больше 3.

Rule of 40

Сумма темпа роста в процентах и рентабельности в процентах. Если больше 40% — SaaS считается здоровым: компания либо быстро растёт, либо прибыльна, либо и то и другое в разумном балансе.

Как это спрашивают на собесе

Интервьюер обычно не просит написать одну гигантскую метрику, а гоняет по определениям и просит набросать запрос. Готовьтесь чётко проговаривать формулы.

«Как посчитать MRR?» — Сумма месячных платежей по всем активным на нужную дату подпискам. Важно проговорить, что это состояние на момент, а не сумма всех платежей за период.

«Что такое churn rate?» — Доля отменённых подписок относительно активной базы на начало периода. Уточните, считаете вы отток по числу клиентов или по выручке.

«Как считать NRR?» — (Начальный MRR + expansion − churn − downgrade) / Начальный MRR по фиксированной когорте клиентов.

«Как оценить LTV?» — Простая оценка: ARPU, делённый на churn rate. Более честная — эмпирическая, по когортам: суммарная выручка когорты, делённая на её размер.

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

• Как посчитать MRR в SQL
• Как посчитать churn в SQL
• MRR / ARR
• Как считать LTV
• SQL для когортного анализа

FAQ

Чем booked MRR отличается от recognized?

Booked MRR — это выручка по подписанным контрактам, которую клиент обязался платить. Recognized MRR — выручка, которую компания фактически признаёт по мере оказания услуги (по правилам учёта). Для аналитики роста обычно смотрят на recognized, чтобы не завышать цифры контрактами, которые ещё не начали приносить деньги.

Как учитывать годовые планы в MRR?

Годовую подписку приводят к месяцу: ARR / 12 = MRR-эквивалент. То есть годовой платёж не засчитывают целиком в MRR месяца оплаты, а размазывают на 12 месяцев — иначе MRR будет скакать и станет непригоден для анализа динамики.

Считать ли freemium-пользователей в MRR?

Нет. MRR — это выручка, и в него попадают только платящие подписки. Бесплатных пользователей отслеживают отдельно как часть воронки и базу для конверсии в платящих, но в MRR они не входят.

Как считать LTV для подписки?

Быстрая оценка: LTV = ARPU / churn rate (средняя выручка с клиента, делённая на долю оттока). Она предполагает постоянный отток и часто завышает результат. Точнее — эмпирический когортный LTV: берут когорту, суммируют всю выручку, которую она принесла за наблюдаемый период, и делят на её размер.

Чем revenue churn отличается от customer churn?

Customer churn — доля ушедших клиентов по количеству. Revenue churn — доля потерянной выручки. Они расходятся, когда клиенты платят разные суммы: можно потерять 5% клиентов, но всего 1% выручки (ушли мелкие), а можно наоборот. У зрелых SaaS revenue churn часто отрицательный за счёт expansion — это и есть NRR больше 100%.

Тренируйте SQL и продуктовую аналитику — откройте тренажёр с 1500+ вопросами для собесов.