Funnel Step Conversion — Solution & ExplanationProblems / Funnel Step Conversion / EditorialApproach

Conditional distinct counts. One pass over events computes all three stage populations: COUNT(DISTINCT CASE WHEN event_type = <stage> THEN user_id END). The CASE maps other rows to NULL, which aggregates ignore, so each count sees only its stage's users.

Step-over-step ratios. From the 1-row CTE, divide adjacent stages: cart/view and purchase/cart, each * 100.0 and rounded to 2 decimals.

PySpark variant. SQLFrame is happiest with three tiny filtered aggregates crossJoined together — same numbers, DataFrame-shaped.

Key InsightFunnels leak *between* consecutive stages, so the informative ratio is step-over-step, not share-of-top. Here 40% of viewers reach the cart but 75% of carters purchase — the cart step is the bottleneck. Both percentages computed against view_users would hide that; the arithmetic difference is one denominator, but the analytical difference is which step you decide to fix.Hints

You need three different filtered populations out of one table, in one row. That is exactly what conditional aggregation does: CASE inside the aggregate, one aggregate per stage.

COUNT(DISTINCT CASE WHEN event_type = 'view' THEN user_id END) works because the CASE emits NULL for non-view rows and both COUNT and DISTINCT ignore NULLs.

DISTINCT is mandatory — users repeat events (multiple views, even two purchases). Without it you count events, not people.

The two percentages divide *adjacent* stages (cart/view, purchase/cart) — not everything by view. Step-over-step shows where the funnel leaks; overall conversion hides it.

Solve Funnel Step Conversion yourself →