Three-step funnel — users at each step and step-to-step conversion
Table events(user_id, step) records funnel steps, where step is one of view, cart, purchase. Return the distinct users who reached each step, plus the two step-to-step conversion rates — view→cart and cart→purchase — as percentages.
-- users per step, plus view->cart and cart->purchase conversionWrite the query.
Count distinct users per step with conditional aggregation — COUNT(DISTINCT user_id) FILTER (WHERE step = 'view') and the same for cart and purchase. Each conversion divides the next step's users by the previous step's — cart over view, purchase over cart.
- ✗Dividing each step by the total base instead of the previous step
- ✗Using COUNT(*) rows instead of COUNT(DISTINCT user_id)
- ✗Confusing step-to-step conversion with end-to-end conversion
- →How would you also report the end-to-end view→purchase conversion?
- →Why prefer FILTER over three separate grouped subqueries?
Use conditional aggregation to count distinct users per step in one pass, then express each conversion relative to the previous step:
SELECT
COUNT(DISTINCT user_id) FILTER (WHERE step = 'view') AS viewed,
COUNT(DISTINCT user_id) FILTER (WHERE step = 'cart') AS carted,
COUNT(DISTINCT user_id) FILTER (WHERE step = 'purchase') AS purchased,
ROUND(100.0 * COUNT(DISTINCT user_id) FILTER (WHERE step = 'cart')
/ COUNT(DISTINCT user_id) FILTER (WHERE step = 'view'), 2) AS view_to_cart_pct,
ROUND(100.0 * COUNT(DISTINCT user_id) FILTER (WHERE step = 'purchase')
/ COUNT(DISTINCT user_id) FILTER (WHERE step = 'cart'), 2) AS cart_to_purchase_pct
FROM events
WHERE step IN ('view', 'cart', 'purchase');COUNT(DISTINCT user_id) (not COUNT(*)) is essential — a user can log the same step twice. Step-to-step conversion divides adjacent steps; dividing by the total base would answer a different question. This "wide" funnel counts anyone who reached a step regardless of order; enforcing the view→cart→purchase order and a time window is a separate, harder query.
