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

Зачем survival rate

Survival rate = доля юзеров, доживших до момента t. У SaaS — это «retention to day N» с учётом всех cohort'ов. В medical это «доля пациентов с success на T месяце». Цепочка: survival → hazard → median lifetime → LTV.

Простой когортный расчёт

Closed cohort: берём юзеров, у которых было минимум T дней наблюдения.

WITH cohort AS (
    SELECT
        user_id,
        start_date,
        event_date,
        CASE
            WHEN event_date IS NOT NULL AND (event_date - start_date) <= 90 THEN 1
            ELSE 0
        END AS churned_within_90
    FROM subscriptions
    WHERE start_date <= CURRENT_DATE - INTERVAL '90 days'
)
SELECT
    COUNT(*) AS cohort_size,
    SUM(churned_within_90) AS churned,
    1 - SUM(churned_within_90)::NUMERIC / COUNT(*) AS survival_at_90d
FROM cohort;

Простая формула, но требует «закрытой» когорты — пользователей с полным наблюдением.

KM-версия

Когда часть юзеров ещё не дожила до T (censored), нужен KM:

WITH user_times AS (
    SELECT
        CASE
            WHEN event_date IS NOT NULL THEN (event_date - start_date)::INT
            ELSE (observed_until - start_date)::INT
        END AS t,
        event_date IS NOT NULL AS event_occurred
    FROM subscriptions
),
risk_set AS (
    SELECT
        t,
        COUNT(*) FILTER (WHERE event_occurred) AS d_i,
        SUM(COUNT(*)) OVER (ORDER BY t DESC) AS n_i
    FROM user_times
    GROUP BY t
)
SELECT
    t,
    EXP(SUM(LN(1 - d_i::NUMERIC / NULLIF(n_i, 0))) OVER (ORDER BY t)) AS survival
FROM risk_set
WHERE n_i > 0
ORDER BY t;

По срезам

Например, по каналу подписки:

WITH user_times AS (
    SELECT
        u.utm_source AS channel,
        CASE
            WHEN s.event_date IS NOT NULL THEN (s.event_date - s.start_date)::INT
            ELSE (s.observed_until - s.start_date)::INT
        END AS t,
        s.event_date IS NOT NULL AS event_occurred
    FROM subscriptions s
    JOIN users u USING (user_id)
),
risk_set AS (
    SELECT
        channel, t,
        COUNT(*) FILTER (WHERE event_occurred) AS d_i,
        SUM(COUNT(*)) OVER (PARTITION BY channel ORDER BY t DESC) AS n_i
    FROM user_times
    GROUP BY channel, t
)
SELECT
    channel,
    30 AS day,
    EXP(SUM(LN(1 - d_i::NUMERIC / NULLIF(n_i, 0))) ) AS survival_at_30
FROM risk_set
WHERE n_i > 0 AND t <= 30
GROUP BY channel
ORDER BY survival_at_30 DESC;

Median survival time

WITH km AS (
    SELECT
        t,
        EXP(SUM(LN(1 - d_i::NUMERIC / NULLIF(n_i, 0))) OVER (ORDER BY t)) AS survival
    FROM risk_set
    WHERE n_i > 0
)
SELECT
    MIN(t) AS median_survival_days
FROM km
WHERE survival <= 0.5;

Если ни одна точка не упала до 0.5 — median > horizon, скажите так в отчёте.

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

Ошибка 1. Использовать simple cohort на смешанной выборке. Если в cohort есть юзеры с разной длиной наблюдения, формула 1 - churned/cohort_size смещена. KM корректнее.

Ошибка 2. Делить на N=0. В первые точки n_i маленькое. NULLIF обязателен.

Ошибка 3. Игнорировать end-of-data censoring. Юзер с start_date=вчера и без event — это censored, не survived.

Ошибка 4. Сравнивать survival на разных horizons. 30d survival vs 90d — несравнимы напрямую. Нормируйте: hazard rate per day.

Ошибка 5. Не сообщать CI. Survival = 70% — звучит точно. CI [55%, 80%] — реалистично. Используйте Greenwood формулу.

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

• Как посчитать Kaplan-Meier в SQL
• Как посчитать hazard rate в SQL
• Как посчитать retention в SQL
• Survival analysis в продуктовой аналитике

FAQ

Survival vs retention?

Retention обычно period-based и закрытая когорта. Survival может работать на смешанной выборке через KM.

N=30 — мало для KM?

Технически работает, но CI огромный. 100+ хотя бы.

Survival по cohort и по acquisition channel — отдельно?

Да: разные каналы дают разные кривые. Считайте по слоям.

Когда KM, когда simple?

Closed cohort — simple. Открытая или mixed — KM.

Survival 60% за 30 дней — много?

Зависит от продукта. SaaS B2B обычно 70-85% на день 30. B2C apps 30-50%.