Постройте упорядоченную воронку view → cart → purchase из сырого лога событий
Таблица events(user_id, event_name, event_time) — сырой лог, где event_name это view, cart или purchase, и пользователь может логировать шаг много раз. Верните уникальных пользователей, дошедших до каждого шага по порядку: cart — первый cart пользователя после его первого view, а purchase — первая покупка после этого cart, каждый в пределах 7 дней от первого view.
-- упорядоченная воронка view -> cart -> purchase из сырого лога
-- дедуп до первого времени шага на пользователя; окно 7 дней от первого viewНапишите запрос.
Сводят лог к одной строке на пользователя с первым временем каждого шага через MIN(event_time) FILTER (...) с GROUP BY user_id. Затем считают на шаг с условиями порядка и окна: cart после view, purchase после cart, каждый в пределах 7 дней от первого view — делая каждый шаг строгим подмножеством верхнего.
- ✗Считать строки событий вместо дедупа до уникальных пользователей на шаг
- ✗Считать принадлежность к шагу без условий порядка или окна
- ✗Опираться на разрыв по модулю, допуская покупку раньше её cart
- →Почему
MIN(event_time) FILTER (...)на пользователя держит счётчики монотонными? - →Как привязать окно 7 дней к соседнему шагу, а не к входу в воронку?
Collapse the raw log to one row per user holding each step's first timestamp, then count with ordering and window guards so each step is a strict subset of the one above:
WITH steps AS (
SELECT
user_id,
MIN(event_time) FILTER (WHERE event_name = 'view') AS t_view,
MIN(event_time) FILTER (WHERE event_name = 'cart') AS t_cart,
MIN(event_time) FILTER (WHERE event_name = 'purchase') AS t_purchase
FROM events
GROUP BY user_id
)
SELECT
COUNT(*) FILTER (WHERE t_view IS NOT NULL) AS viewed,
COUNT(*) FILTER (
WHERE t_cart > t_view
AND t_cart <= t_view + INTERVAL '7 days'
) AS carted,
COUNT(*) FILTER (
WHERE t_cart > t_view
AND t_cart <= t_view + INTERVAL '7 days'
AND t_purchase > t_cart
AND t_purchase <= t_view + INTERVAL '7 days'
) AS purchased
FROM steps;MIN(event_time) FILTER (...) with GROUP BY user_id gives each user one timestamp per step, so repeat events cannot inflate a count. The directional predicates (t_cart > t_view, t_purchase > t_cart) enforce the view→cart→purchase order, and <= t_view + INTERVAL '7 days' bounds the whole conversion to a 7-day window from entry. Because every purchased row also satisfies the carted condition, the counts are monotonic by construction — you can never report more purchases than carts.
