A Conversion Funnel

The interview question: “Users view a product, add it to the cart, then buy. How many reach each step, and where do we lose the most?” That is a conversion funnel: steps in a fixed order, and for each step the number of users who got that far. Product teams decide what to fix from it, so a wrong funnel sends them to fix the wrong page.

The trap is to count the events with GROUP BY event. Here that gives 8 views, 6 carts and 6 purchases. User 3's two views count twice, and users 6, 7 and 8 count as buyers although none of them went view, cart, buy in that order. So the step from cart to purchase seems to lose nobody.

The fix starts with one row per user, holding the first time they reached each step: GROUP BY user_id and a conditional aggregate, MIN(CASE WHEN … THEN event_at END). The CASE keeps the time only on the rows of that event (NULL on the others), and MIN takes the earliest. A user who never did the event gets NULL. For another event it would read:

MIN(CASE WHEN event = 'login' THEN event_at END) AS first_login

Then you count in order: a user counts at the cart step only when viewed < carted, using COUNT(CASE WHEN … THEN 1 END). COUNT skips NULLs, a CASE without ELSE gives NULL when its test fails, and any comparison with a NULL is NULL. So users who skipped a step drop out on their own. The drop between two steps is 1 minus the share who went on. DuckDB's / keeps the decimals: 5 / 7 is 0.714…, not 0.

The events, events.csv, written for this lesson, then what the check prints (user_steps, then your funnel):

user_id,event,event_at
1,view,2026-05-04 09:00:00
1,cart,2026-05-04 09:04:00
1,purchase,2026-05-04 09:09:00
2,view,2026-05-04 09:15:00
2,cart,2026-05-04 09:20:00
2,purchase,2026-05-04 09:31:00
3,view,2026-05-04 10:02:00
3,view,2026-05-04 10:05:00
3,cart,2026-05-04 10:07:00
3,purchase,2026-05-04 10:12:00
4,view,2026-05-04 10:30:00
4,cart,2026-05-04 10:41:00
5,view,2026-05-04 11:00:00
6,view,2026-05-04 11:20:00
6,purchase,2026-05-04 11:24:00
7,cart,2026-05-04 12:00:00
7,purchase,2026-05-04 12:05:00
8,view,2026-05-04 13:00:00
8,purchase,2026-05-04 13:06:00
8,cart,2026-05-04 13:10:00

user_id | viewed | carted | bought
1 | 2026-05-04 09:00:00 | 2026-05-04 09:04:00 | 2026-05-04 09:09:00
2 | 2026-05-04 09:15:00 | 2026-05-04 09:20:00 | 2026-05-04 09:31:00
3 | 2026-05-04 10:02:00 | 2026-05-04 10:07:00 | 2026-05-04 10:12:00
4 | 2026-05-04 10:30:00 | 2026-05-04 10:41:00 | NULL
5 | 2026-05-04 11:00:00 | NULL | NULL
6 | 2026-05-04 11:20:00 | NULL | 2026-05-04 11:24:00
7 | NULL | 2026-05-04 12:00:00 | 2026-05-04 12:05:00
8 | 2026-05-04 13:00:00 | 2026-05-04 13:10:00 | 2026-05-04 13:06:00
(8 rows)
views | carts | purchases | cart_drop | purchase_drop
7 | 5 | 3 | 0.29 | 0.4
(1 row)

Your task: add each user's first cart and purchase times to user_steps. Then count the users who reached the cart and the purchase in order, and work out the two drops, rounded to 2 decimals.

Assignment
Follow the Coding Tutorial to finish user_steps and the funnel query: first the cart and purchase times, then the counts in order, then cart_drop and purchase_drop.

Hint
Copy the shape of the viewed line for carted and bought. A purchase counts only when both viewed < carted and carted < bought hold, and user 8 bought before carting.