Write a query to return every ordered step in funnel_counts, keeping step_no, step_name and users, with:
01pct_of_previous: Current users divided by the preceding step’s users, multiplied by 100 and rounded to one decimal place. Return NULL for the first step or a zero preceding count.
02dropped: Preceding users minus current users; NULL for the first step.
03pct_of_entrants: Current users divided by the first step’s users, multiplied by 100 and rounded to one decimal place.
Keep zero-count steps and report the arithmetic unchanged, including percentages above 100 and negative drop-off when counts increase. Do not cap or correct the supplied counts.
Ordering: Sort by step_no ascending.
Returnstep_nostep_nameuserspct_of_previousdroppedpct_of_entrants
About this problem
You practise the arithmetic a funnel report is made of, on a table where the counting is already done. Three columns: the step-to-step rate, the loss at each step, and the rate against the people who entered. The skill is small and it is the one most often got slightly wrong, because every mistake here produces a number rather than an error.
Two of those mistakes are worth meeting on purpose. The first is the denominator for pct_of_entrants. max(users) is very nearly right – it gives the same answer for every funnel that only ever goes down, which is almost all of them, and a different answer for this one. This funnel goes up at step 2 because add-to-cart fires again on a quantity change, and that is exactly the kind of data problem a funnel report exists to surface. A query that takes the largest count as the entrant count quietly normalises the anomaly away and reports 95.9% at step 1, which is not a percentage of anything. The second is division by zero. Checkout tracking broke, so two steps are empty, and the step after an empty one has nothing to divide by. DuckDB answers that with NaN rather than an error, so the query runs, the row renders, and a meaningless value travels onward.
Read the lesson: Funnels & Conversion (P22) →