Build an ordered view → cart → purchase funnel from a raw event log
Table events(user_id, event_name, event_time) is a raw log where event_name is one of view, cart, or purchase, and a user may log a step many times. Return the distinct users who reached each step in order: cart must be the user's first cart after their first view, and purchase their first purchase after that cart — each within 7 days of the first view.
-- ordered view -> cart -> purchase funnel from the raw log
-- dedup to each user's first time per step; 7-day window from first viewWrite the query.
Reduce the log to one row per user with each step's first timestamp via MIN(event_time) FILTER (...) grouped by user_id. Then count per step with ordering and window guards: cart after view, purchase after cart, each within 7 days of the first view — making each step a strict subset of the one above.
- ✗Counting event rows instead of deduping to distinct users per step
- ✗Counting step membership with no ordering or window constraint
- ✗Anchoring on an absolute gap that lets a purchase precede its cart
- →Why does
MIN(event_time) FILTER (...)per user keep the step counts monotonic? - →How would you anchor the 7-day window per adjacent step instead of from entry?
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.
