Building a conversion funnel in SQL

WHAT THIS TESTS Whether you can author a correct funnel query and reason precisely about where users are lost, distinguishing absolute counts from step-to-step conversion.

A GOOD ANSWER COVERS Reduce the event stream to one row per user capturing the first timestamp of each step, using conditional aggregation with MIN of a CASE WHEN per event name. Enforce ordering so each step's timestamp is greater than the previous step's, otherwise out-of-order events inflate later steps. Count distinct users who have a non-null timestamp for each step, and satisfy ordering, to get the count at each stage. Then compute conversion: step N count over step N minus 1 count gives the step-to-step rate, while step N over step 1 gives overall progression. Always count distinct users, never raw events.

PSEUDO-SQL WITH per_user AS (SELECT user_id, MIN(CASE WHEN event_name = step1 THEN ts END) s1, MIN(CASE WHEN event_name = step2 THEN ts END) s2, MIN(CASE WHEN event_name = step3 THEN ts END) s3 FROM events GROUP BY user_id) SELECT COUNT(s1) step1, COUNT(CASE WHEN s2 >= s1 THEN 1 END) step2, COUNT(CASE WHEN s3 >= s2 THEN 1 END) step3 FROM per_user. Then derive step2 over step1 and step3 over step2 as the consecutive conversion rates.

IDENTIFYING THE BIGGEST DROP-OFF The biggest drop-off is the consecutive step pair with the lowest step-to-step conversion rate, equivalently the largest relative loss between adjacent stages. Do not confuse this with the step that has the fewest users overall, since later steps always have fewer, or with the largest absolute count difference.

COMMON WRONG ANSWERS Counting events instead of distinct users. Comparing each step against the original cohort and calling the last step the biggest drop. Ignoring ordering. Using the smallest absolute count as the leak.

ONE CONCRETE EXAMPLE Step1 10,000, step2 8,500, step3 3,000. Step1to2 is 85 percent, step2to3 is 35 percent, so the worst leak is between steps 2 and 3, even though every step naturally has fewer users than the one before it.