Как посчитать Kaplan-Meier в SQL

Зачем Kaplan-Meier

Стандартная retention считает «сколько юзеров активны на день N», но не учитывает censored data — юзеров, которые только signup-нули 3 дня назад. Kaplan-Meier (KM) — survival curve, которая корректно обрабатывает right-censored наблюдения и работает на разнородных когортах.

Применение в продукте: «через сколько дней половина trial-users отвалится», «выживаемость по плану подписки», «risk of churn по cohort'у».

Базовые данные

Для каждого юзера:

• start_date — момент входа в риск-set (signup, subscription_start)
• event_date — момент события (churn, downgrade) или NULL
• observed_until — дата последнего наблюдения (CURRENT_DATE или дата экспорта)

user_id | start_date | event_date | observed_until
42      | 2026-01-01 | 2026-03-15 | 2026-03-15
99      | 2026-01-15 | NULL       | 2026-05-13   -- censored

Формула

S(t) = Π over t_i ≤ t of (1 − d_i / n_i)

• n_i — at-risk на момент t_i
• d_i — число events в точке t_i

Censored юзеры остаются в «at risk» до их observed_until, но не считаются events.

Kaplan-Meier в SQL

WITH user_times AS (
    SELECT
        user_id,
        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
    WHERE start_date >= '2026-01-01'
),
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
),
km AS (
    SELECT
        t,
        n_i,
        d_i,
        1 - d_i::NUMERIC / NULLIF(n_i, 0) AS survival_step,
        EXP(SUM(LN(1 - d_i::NUMERIC / NULLIF(n_i, 0))) OVER (ORDER BY t)) AS survival
    FROM risk_set
)
SELECT t, n_i, d_i, survival
FROM km
ORDER BY t;

Трюк через EXP(SUM(LN(...))) заменяет product (которого нет встроенного в Postgres).

Сравнение групп

KM по группам (например, тариф Standard vs Premium):

WITH user_times AS (
    SELECT
        plan,
        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
    WHERE start_date >= '2026-01-01'
),
risk_set AS (
    SELECT
        plan, t,
        COUNT(*) FILTER (WHERE event_occurred) AS d_i,
        SUM(COUNT(*)) OVER (PARTITION BY plan ORDER BY t DESC) AS n_i
    FROM user_times
    GROUP BY plan, t
)
SELECT
    plan,
    t,
    EXP(SUM(LN(1 - d_i::NUMERIC / NULLIF(n_i, 0))) OVER (PARTITION BY plan ORDER BY t)) AS survival
FROM risk_set
ORDER BY plan, t;

Для строгого сравнения групп — log-rank test (вне SQL).

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

Ошибка 1. Не учитывать censoring. Если просто считать «среди тех, кто отвалился» — занижаете survival.

Ошибка 2. LN(0). Если в точке t все юзеры отваливаются (d_i = n_i), 1 − 1 = 0, LN(0) = −∞. Фильтруйте такие точки или обрабатывайте отдельно.

Ошибка 3. n_i через cumulative running sum в неправильную сторону. At-risk на момент t_i = все юзеры с t >= t_i. Это reverse cumulative, не обычный.

Ошибка 4. Дискретизировать слишком грубо. Если все события на day-level, а у вас несколько событий в один день — KM учитывает как одну точку.

Ошибка 5. Confidence interval не считать. KM с CI стандартен через Greenwood формулу. Без CI кривая «вертится в воздухе».

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

• Как посчитать hazard rate в SQL
• Как посчитать survival rate в SQL
• Survival analysis на собесе DS
• Что такое survival analysis простыми словами

FAQ

KM vs обычная retention curve?

Retention обычно on closed cohort (только тех, у кого было N дней). KM работает на смешанной выборке (включая partial observations).

Когда нужна KM?

Когда у части юзеров наблюдение неполное (right-censored): юзеры новее, чем horizon анализа.

Median survival time?

Время, при котором S(t) = 0.5. Чтобы найти в SQL — WHERE survival <= 0.5 ORDER BY t LIMIT 1.

Log-rank в SQL?

Сложно. Лучше вынести в Python lifelines.

KM для retention — норма?

Да, особенно для cohort'ов с разной длиной наблюдения.