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

Зачем аналитику считать churn в SQL

Churn — метрика, которую ваш CEO смотрит каждый понедельник. В SaaS churn напрямую определяет LTV: LTV = ARPU / churn. Рост churn на 1 пункт может убить unit-экономику. Поэтому умение правильно посчитать churn в SQL — обязательный навык для аналитика подписочного продукта.

Но тут много нюансов. Customer churn ≠ revenue churn. Voluntary ≠ involuntary. Monthly ≠ annual. Общий churn маскирует когортные проблемы. Если считать «в лоб», легко получить цифру, которая выглядит нормально, но скрывает реальные проблемы.

В статье — готовые SQL-запросы для всех частых случаев:

• Monthly customer churn
• Revenue churn и Net Revenue Retention (NRR)
• Churn по когортам (видно тренд улучшения продукта)
• Voluntary vs involuntary (сколько теряем из-за expired cards)
• Пересчёт monthly → annual

Вставьте в Metabase или dbt — будете считать правильно.

Формула

Churn rate = ушли за период / было в начале

1. Monthly customer churn

WITH monthly AS (
    SELECT
        user_id,
        DATE_TRUNC('month', updated_at) AS month,
        status
    FROM subscriptions
)
SELECT
    month,
    COUNT(DISTINCT user_id) FILTER (WHERE status = 'churned')::FLOAT /
    NULLIF(COUNT(DISTINCT user_id), 0) AS churn_rate
FROM monthly
GROUP BY month
ORDER BY month;

2. Retention-based churn

Churn = 1 - retention. Если в начале месяца было N юзеров, а на конец активны M:

WITH cohort_start AS (
    SELECT user_id
    FROM users
    WHERE signup_at <= '2026-03-01'
      AND (churned_at IS NULL OR churned_at > '2026-03-01')
),
cohort_end AS (
    SELECT user_id
    FROM cohort_start
    WHERE user_id IN (
        SELECT DISTINCT user_id FROM events WHERE event_at <= '2026-03-31'
    )
)
SELECT
    1.0 - COUNT(DISTINCT cohort_end.user_id)::FLOAT /
    COUNT(DISTINCT cohort_start.user_id) AS churn_rate_march
FROM cohort_start
LEFT JOIN cohort_end USING (user_id);

3. Revenue churn

WITH mrr_start AS (
    SELECT SUM(mrr) AS mrr_start
    FROM subscriptions_snapshot
    WHERE snapshot_date = '2026-03-01'
),
mrr_churned AS (
    SELECT SUM(s.mrr) AS mrr_lost
    FROM subscriptions s
    WHERE s.status = 'churned'
      AND DATE_TRUNC('month', s.churned_at) = '2026-03-01'
)
SELECT
    mrr_lost / mrr_start AS revenue_churn_rate
FROM mrr_start, mrr_churned;

4. Net Revenue Retention (NRR)

WITH mrr_movements AS (
    SELECT
        SUM(CASE WHEN event_type = 'start' THEN mrr ELSE 0 END) AS new_mrr,
        SUM(CASE WHEN event_type = 'expansion' THEN mrr ELSE 0 END) AS expansion,
        SUM(CASE WHEN event_type = 'contraction' THEN mrr ELSE 0 END) AS contraction,
        SUM(CASE WHEN event_type = 'churn' THEN mrr ELSE 0 END) AS churn
    FROM mrr_events
    WHERE DATE_TRUNC('month', event_at) = '2026-03-01'
),
mrr_start AS (
    SELECT SUM(mrr) AS start_mrr FROM subs WHERE snapshot_date = '2026-03-01'
)
SELECT
    (start_mrr + expansion - contraction - churn) / start_mrr AS nrr
FROM mrr_movements, mrr_start;

NRR > 1 — рост даже без новых.

5. Churn по cohort

WITH cohorts AS (
    SELECT user_id, DATE_TRUNC('month', signup_at) AS cohort
    FROM users
),
activity AS (
    SELECT DISTINCT user_id, DATE_TRUNC('month', event_at) AS activity_month
    FROM events
)
SELECT
    c.cohort,
    a.activity_month,
    EXTRACT(MONTH FROM AGE(a.activity_month, c.cohort)) AS months_since,
    COUNT(DISTINCT a.user_id) AS active,
    COUNT(DISTINCT a.user_id)::FLOAT /
        (SELECT COUNT(*) FROM cohorts WHERE cohort = c.cohort) AS retention
FROM cohorts c
LEFT JOIN activity a ON a.user_id = c.user_id
GROUP BY c.cohort, a.activity_month
ORDER BY c.cohort, a.activity_month;

6. Voluntary vs involuntary churn

SELECT
    CASE
        WHEN churn_reason IN ('card_expired', 'payment_failed') THEN 'involuntary'
        ELSE 'voluntary'
    END AS churn_type,
    COUNT(*) AS churned
FROM subscriptions
WHERE status = 'churned'
  AND DATE_TRUNC('month', churned_at) = '2026-03-01'
GROUP BY 1;

7. Monthly vs annual churn

Monthly churn 5% ≠ annual 60%:

-- monthly compound → annual
SELECT 1 - POWER(1 - 0.05, 12) AS annual_churn;
-- ≈ 0.46 = 46%

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

• Считать только по активным (survivorship bias)
• Путать customer и revenue churn
• Смешивать monthly и annual без пересчёта
• Забывать про involuntary (expired cards)
• Не учитывать cohorts

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

• Churn простыми словами
• Retention простыми словами
• Retention vs churn
• Кейс: churn вырос

FAQ

Customer или revenue churn?

Revenue для unit economics. Customer — для общей картины.

Monthly или quarterly?

SaaS с месячной оплатой — monthly. Enterprise с годовыми — annual.

---

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