Drop-Off Report — Solution & ExplanationProblems / Drop-Off Report / EditorialApproach

Distinct users per step. Group by step_number, step_name and take COUNT(DISTINCT user_id). The data plants duplicate rows (a user hitting the same step twice), so a plain COUNT(*) inflates the funnel.

The step-1 base. pct_of_first divides every step by one shared number — the step-1 audience. In SQL that is a scalar subquery over the same CTE; in PySpark, filter the step-1 row into a 1-row DataFrame and crossJoin it onto all four rows.

Round to 2 decimals and order by step_number.

Key InsightFunnel tables are event logs, not user lists — the unit you count must be the *user*, so DISTINCT is not optional. And "percent of first step" is a whole-report constant, not a per-row calculation: computing it once (CTE + scalar subquery / crossJoin) keeps the query honest and makes the 100.00 on step 1 fall out naturally.Hints

Two aggregations are hiding here: distinct users per step, and one special number — the distinct users at step 1 — that every row divides by.

COUNT(*) is a trap: the tracker double-fired for some users, so the log has duplicate (user, step) rows. Only COUNT(DISTINCT user_id) gives true audience sizes.

Compute the per-step counts in a CTE first. Then the step-1 base is a scalar subquery against that same CTE (in PySpark: filter the step-1 row into a 1-row DataFrame and crossJoin it on).

Solve Drop-Off Report yourself →