Условие

В таблице events(user_id, event_dt, event_type) есть события:

signup
email_confirm
card_added
first_payment

Постройте воронку (5 шагов с signup): для каждой когорты по дате signup посчитайте, сколько пользователей дошли до каждого шага в течение 7 дней после signup. Покажите conv rate шаг-в-шаг и absolute conversion от signup.

Решение

Подход

Для каждого user_id найти signup_date (первый signup).
Для каждого следующего события — проверить, что оно случилось в течение 7 дней после signup.
Сгруппировать по cohort = date_trunc('week', signup_date).

Запрос

WITH signup AS (
    SELECT user_id,
           MIN(event_dt) AS signup_dt,
           date_trunc('week', MIN(event_dt))::date AS cohort_week
    FROM events
    WHERE event_type = 'signup'
    GROUP BY user_id
),
reached AS (
    SELECT
        s.cohort_week,
        s.user_id,
        MAX(CASE WHEN e.event_type='signup'         THEN 1 ELSE 0 END) AS r_signup,
        MAX(CASE WHEN e.event_type='email_confirm'  THEN 1 ELSE 0 END) AS r_email,
        MAX(CASE WHEN e.event_type='card_added'     THEN 1 ELSE 0 END) AS r_card,
        MAX(CASE WHEN e.event_type='first_payment'  THEN 1 ELSE 0 END) AS r_pay
    FROM signup s
    LEFT JOIN events e
           ON e.user_id = s.user_id
          AND e.event_dt BETWEEN s.signup_dt AND s.signup_dt + INTERVAL '7 day'
    GROUP BY s.cohort_week, s.user_id
),
agg AS (
    SELECT
        cohort_week,
        COUNT(*)                              AS n_signup,
        SUM(r_email)                          AS n_email,
        SUM(r_card)                           AS n_card,
        SUM(r_pay)                            AS n_pay
    FROM reached
    GROUP BY cohort_week
)
SELECT
    cohort_week,
    n_signup, n_email, n_card, n_pay,
    ROUND(n_email * 100.0 / n_signup, 1)  AS conv_signup_to_email,
    ROUND(n_card  * 100.0 / NULLIF(n_email, 0), 1) AS conv_email_to_card,
    ROUND(n_pay   * 100.0 / NULLIF(n_card,  0), 1) AS conv_card_to_pay,
    ROUND(n_pay   * 100.0 / n_signup, 1)  AS conv_signup_to_pay
FROM agg
ORDER BY cohort_week;

Альтернатива: «strict funnel» — пользователь шёл по шагам в правильном порядке

WITH ordered AS (
    SELECT
        user_id, event_dt, event_type,
        LAG(event_dt) OVER (PARTITION BY user_id ORDER BY event_dt) AS prev_dt,
        LAG(event_type) OVER (PARTITION BY user_id ORDER BY event_dt) AS prev_type
    FROM events
)
SELECT
    SUM(CASE WHEN event_type='signup'        THEN 1 END) AS step1,
    SUM(CASE WHEN event_type='email_confirm' AND prev_type='signup'        THEN 1 END) AS step2,
    SUM(CASE WHEN event_type='card_added'    AND prev_type='email_confirm' THEN 1 END) AS step3,
    SUM(CASE WHEN event_type='first_payment' AND prev_type='card_added'    THEN 1 END) AS step4
FROM ordered;

— но это уже «строгая» воронка, где требуется последовательность. Чаще бизнесу нужна «soft» (просто факт события в окне).

Подводные камни

«В течение 7 дней» — относительно signup или предыдущего шага? Если «7 дней от signup до first_payment» — фильтр от signup_dt. Если «7 дней между шагами» — фильтр LAG'ом.
Пользователи могут регистрироваться дважды — MIN(event_dt) WHERE event_type='signup' берёт первый.
Strict vs soft funnel: soft — пользователь долетел до шага вне зависимости от порядка; strict — шёл строго по последовательности.
Когорта < 7 дней назад «не созрела» — у них ещё может появиться first_payment. Помечать как «in progress» или исключать.
MAX(CASE WHEN ... THEN 1 ELSE 0) vs BOOL_OR: эквивалентно, BOOL_OR более «семантично» в Postgres.
Часовые пояса: event_dt + INTERVAL '7 day' — корректно для timestamp; для timestamptz нужно осторожно.
Множественные «first_payment»: брать первый по времени → не нужно дублирование при подсчёте.

Эталонный ответ

-- soft funnel за 7 дней от signup
WITH signup AS (...),                      -- первый signup на пользователя
reached AS (                               -- факт долёта до каждого шага
    SELECT s.cohort_week, s.user_id,
           BOOL_OR(e.event_type='email_confirm') AS r_email,
           BOOL_OR(e.event_type='card_added')    AS r_card,
           BOOL_OR(e.event_type='first_payment') AS r_pay
    FROM signup s LEFT JOIN events e
      ON e.user_id=s.user_id
     AND e.event_dt BETWEEN s.signup_dt AND s.signup_dt + INTERVAL '7 day'
    GROUP BY s.cohort_week, s.user_id
)
SELECT cohort_week, COUNT(*) AS signups,
       SUM(r_email::int)*100.0/COUNT(*) AS pct_to_email,
       SUM(r_card::int)*100.0/COUNT(*)  AS pct_to_card,
       SUM(r_pay::int)*100.0/COUNT(*)   AS pct_to_pay
FROM reached GROUP BY 1 ORDER BY 1;

Ключи: cohort по signup_dt, окно 7 дней от signup, факт долёта через BOOL_OR / MAX(CASE). Конверсия шаг-в-шаг — через деление соседних. Свежие когорты не «созрели» — пометить отдельно.

Karpov ДЗ: Конверсия по воронке регистрация → оплата