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

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

SaaS / subscription growing sector. Карьерник, Skyeng, Okko, Ivi, Hulu equivalents — все имеют subscription model. Analytics скилы для SaaS — в demand.

Unique metrics: MRR, ARR, churn, NRR, cohort retention. Без SQL нельзя корректно считать.

Схема данных

-- 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)

Current MRR:

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

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 = MRR × 12.

Churn rate

Monthly 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;

Good SaaS: <2% monthly.

Net Revenue Retention (NRR)

Classic SaaS:

NRR = (Starting MRR + expansion - churn - downgrade) / Starting 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% — holy grail (expansion > churn).

Cohort retention для subscription

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;

Normalize by cohort size → 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;

Plan upgrades / downgrades

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;

Payback period

Through many customers:

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 marketing
    FROM users_data
)
SELECT AVG(
    acquisition_cost / (lifetime_revenue / tenure_months)
) AS avg_payback_months
FROM customer_months
WHERE tenure_months > 0;

Good SaaS — < 12 months.

Reactivation

Users who cancelled, came back:

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

From existing customers (upsell, additional seats):

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;

Customer health score

Combine signals:

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;

Used CS teams для proactive outreach.

Ключевые SaaS ratios

CAC payback

CAC / Monthly revenue per customer.

Good: < 12 months.

LTV / CAC

LTV divided by CAC.

Good: > 3.

Rule of 40

Growth % + Profit margin %.

На собесе

«MRR как считать?»

По месяцу: sum of monthly amounts active subscriptions.

«Churn rate»

Cancelled / active at start of period.

«NRR»

(Start MRR + expansion - churn - downgrade) / Start MRR.

«LTV»

ARPU / churn rate. Cohort-based — empirical.

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

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

FAQ

Booked vs recognized MRR?

Booked — contracts signed. Recognized — actually billing. Accounting.

Annual plans MRR?

ARR / 12 = MRR equivalent.

Freemium в MRR?

No — MRR = paying только. Track separately freemium.

---

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