Условие

Таблица events(user_id, event_name, event_time timestamp). Воронка из шагов: view → add_to_cart → checkout → purchase. Посчитайте:

сколько уникальных пользователей дошло до каждого шага,
конверсию между соседними шагами,
общую конверсию view → purchase.

Шаг засчитывается, только если он произошёл после предыдущего шага этого пользователя.

Решение

Подход

Для каждого пользователя ищем «время первого view», «первое add_to_cart после view», «первый checkout после add_to_cart» и так далее.

WITH steps AS (
  SELECT
    user_id,
    MIN(CASE WHEN event_name = 'view'         THEN event_time END) AS t_view,
    MIN(CASE WHEN event_name = 'add_to_cart'  THEN event_time END) AS t_atc,
    MIN(CASE WHEN event_name = 'checkout'     THEN event_time END) AS t_chk,
    MIN(CASE WHEN event_name = 'purchase'     THEN event_time END) AS t_buy
  FROM events
  GROUP BY user_id
),
ordered AS (
  SELECT
    user_id,
    t_view,
    CASE WHEN t_atc > t_view THEN t_atc END AS t_atc,
    CASE WHEN t_chk > t_atc AND t_atc > t_view THEN t_chk END AS t_chk,
    CASE WHEN t_buy > t_chk AND t_chk > t_atc AND t_atc > t_view THEN t_buy END AS t_buy
  FROM steps
)
SELECT
  COUNT(*) FILTER (WHERE t_view IS NOT NULL)                              AS step1_view,
  COUNT(*) FILTER (WHERE t_atc  IS NOT NULL)                              AS step2_atc,
  COUNT(*) FILTER (WHERE t_chk  IS NOT NULL)                              AS step3_chk,
  COUNT(*) FILTER (WHERE t_buy  IS NOT NULL)                              AS step4_buy,
  ROUND(100.0 * COUNT(*) FILTER (WHERE t_atc IS NOT NULL)
              / NULLIF(COUNT(*) FILTER (WHERE t_view IS NOT NULL), 0), 2) AS cr_view_to_atc,
  ROUND(100.0 * COUNT(*) FILTER (WHERE t_buy IS NOT NULL)
              / NULLIF(COUNT(*) FILTER (WHERE t_view IS NOT NULL), 0), 2) AS cr_view_to_buy
FROM ordered;

Альтернатива — оконные функции

Можно использовать LAG/LEAD по упорядоченным событиям и считать «достиг ли шаг N после шага N-1». Удобно, когда нужно учитывать сессии.

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

Без проверки порядка. Если пользователь добавил в корзину, потом удалил, потом снова посмотрел и купил без add_to_cart — это не «прошёл воронку».
MIN() без условия порядка. Самая частая ошибка: посчитать просто COUNT(DISTINCT user_id) WHERE event_name = ... для каждого шага — игнорирует порядок и переоценивает конверсию.
Сессионная агрегация. В реальности воронка — внутри одной сессии, не lifetime. Добавьте окно по session_id или event_time в пределах 30 минут.
Дубликаты событий. add_to_cart может срабатывать дважды от клика; MIN спасает, но COUNT — нет.
Конверсия = step_i / step_1 vs step_i / step_{i-1} — оба валидные метрики, но разные. Уточняйте.

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

Условная агрегация MIN(CASE WHEN ... END) для времени каждого шага + проверка возрастания времён + COUNT(*) FILTER (WHERE step IS NOT NULL) — самый читаемый паттерн воронки.

Хабр SQL — воронка с конверсиями шаг-к-шагу