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

Зачем cumulative incidence

Cumulative incidence (CI, не путать с confidence interval) = 1 − S(t). Это накопленная вероятность того, что событие произошло к моменту t. В продукте обычное представление: «накопленная конверсия в paid», «доля юзеров, отвалившихся к day 90». Удобно для отчётности — легче читается, чем «survival 60%».

Формула

CI(t) = 1 − S(t) = 1 − Π (1 − d_i/n_i)

Где S(t) — survival function (Kaplan-Meier).

Cumulative incidence в SQL

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 trial_conversions
    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,
        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
    t,
    1 - survival AS cumulative_incidence
FROM km
ORDER BY t;

Если в момент t=14 cumulative_incidence = 0.35 — 35% юзеров конвертнулись в paid к 14 дню.

Competing risks

В реальности у юзера может произойти несколько типов событий: paid, churn, downgrade. Каждый «забирает» юзера из at-risk. Простой KM считает все события одной категорией. Для разных событий — competing risks:

WITH user_times AS (
    SELECT
        user_id,
        CASE
            WHEN event_type IS NOT NULL THEN (event_date - start_date)::INT
            ELSE (observed_until - start_date)::INT
        END AS t,
        event_type
    FROM trial_outcomes
),
risk_set AS (
    SELECT
        t,
        COUNT(*) FILTER (WHERE event_type = 'paid')       AS d_paid,
        COUNT(*) FILTER (WHERE event_type = 'churn')      AS d_churn,
        SUM(COUNT(*)) OVER (ORDER BY t DESC) AS n_i
    FROM user_times
    GROUP BY t
)
SELECT
    t,
    SUM(d_paid::NUMERIC / NULLIF(n_i, 0)) OVER (ORDER BY t)  AS cum_incidence_paid,
    SUM(d_churn::NUMERIC / NULLIF(n_i, 0)) OVER (ORDER BY t) AS cum_incidence_churn
FROM risk_set
ORDER BY t;

Грубая аппроксимация: точный competing risks-расчёт через Aalen-Johansen.

По срезам

Cumulative incidence к 30 дням по каналу:

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 trial_conversions s
    JOIN users u USING (user_id)
    WHERE s.start_date >= CURRENT_DATE - INTERVAL '120 days'
),
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,
    1 - EXP(SUM(LN(1 - d_i::NUMERIC / NULLIF(n_i, 0))) FILTER (WHERE t <= 30) OVER (PARTITION BY channel ORDER BY t)) AS ci_at_30
FROM risk_set
WHERE n_i > 0
ORDER BY channel;

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

Ошибка 1. Cumulative incidence = простая cumulative count / cohort size. Это «raw conversion», не cumulative incidence в strict-смысле. CI учитывает censoring.

Ошибка 2. Игнорировать competing risks. Если юзер мог стать paid ИЛИ churn'нуть, и вы считаете «paid CI» через 1-KM(paid) — это переоценка. Юзеры, churn-нувшие, не должны быть at-risk для paid.

Ошибка 3. Применять KM к non-terminating события. Login event может повторяться. Survival analysis — для terminal событий (paid happens once, churn happens once).

Ошибка 4. CI > 1. Если в SQL ошибка — иногда видно > 100% в выводе. Знак, что формула нарушена (forward sum вместо backward, дубли).

Ошибка 5. Сравнивать CI разных событий без альтернатив. «CI paid 40%, CI churn 60%» — сумма 100%? Может быть и больше, если censoring. Используйте competing risks.

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

• Как посчитать Kaplan-Meier в SQL
• Как посчитать survival rate в SQL
• Как посчитать hazard rate в SQL
• Как посчитать trial conversion в SQL

FAQ

CI vs raw conversion rate?

Raw — converted / total cohort. CI учитывает censoring и time-to-event.

Competing risks обязательны?

Когда событий несколько — да. Один simple KM на каждое событие отдельно — biased.

CI всегда < 1?

Да, по определению. > 1 — баг.

Какой horizon выбрать?

Минимум 1 типичный «buying cycle». Для SaaS B2B 90 дней, для apps 30 дней.

CI = retention?

Это «1 − retention» если событие — churn. Для других событий — другая интерпретация.