Multi-entry funnel — attribute each user once to their furthest step in the window
Table events(user_id, event_type, event_time) with steps view, cart, purchase. Users re-enter the funnel and repeat steps. Over one analysis window, attribute each user exactly once — to the furthest step they reached — and report a cumulative funnel (how many users reached at least each step).
-- each user counted once at their furthest step, cumulative funnelWrite the query.
Rank the steps (view=1, cart=2, purchase=3) and take MAX(step_rank) per user in the window — collapsing every re-entry into one furthest step. Then count users with FILTER (WHERE max_step >= k) per k — a cumulative funnel counting each user once, at their deepest step.
- ✗Assuming distinct-per-step counts already handle re-entry correctly
- ✗Taking the chronologically last event as the furthest step
- ✗Summing step ranks so repeat visits outrank a single deep pass
- →Why can a plain distinct-per-step funnel double-count a re-entering user?
- →How would you restrict the furthest step to a single session, not the whole window?
Reduce each user to a single number — the rank of the furthest step they reached in the window — then build the cumulative funnel from that per-user maximum:
WITH furthest AS (
SELECT user_id,
MAX(CASE event_type
WHEN 'view' THEN 1
WHEN 'cart' THEN 2
WHEN 'purchase' THEN 3
END) AS max_step
FROM events
WHERE event_time >= DATE '2024-01-01'
AND event_time < DATE '2024-02-01'
GROUP BY user_id
)
SELECT
COUNT(*) FILTER (WHERE max_step >= 1) AS reached_view,
COUNT(*) FILTER (WHERE max_step >= 2) AS reached_cart,
COUNT(*) FILTER (WHERE max_step >= 3) AS reached_purchase
FROM furthest;MAX(step_rank) per user is the crux: re-entries and repeated steps collapse into one furthest point, so a user who looped view→cart→view is still attributed once, at cart. The >= k filters make it cumulative and monotonic (reached_view >= reached_cart >= reached_purchase). A naive COUNT(DISTINCT user_id) per step can double-count intent and does not guarantee monotonicity when steps are skipped; taking the last event by time is wrong because the final logged step is often a backtrack, not the deepest one.
