Purchase funnel conversion

Hard

Question

An online shop tracks what visitors do in the events table. The three steps of the funnel are, in order: view, add_to_cart and purchase.

For every user, take the FIRST time they did each action. A user reaches step 1 if they have a first view. They reach step 2 only if they reached step 1 and their first add_to_cart is later than their first view. They reach step 3 only if they reached step 2 and their first purchase is later than their first add_to_cart. Users can do the same action many times, and each user counts once per step.

Return three rows with step_no (1, 2, 3), step (the action name), users (how many users reached the step) and pct_of_viewers (users as a percentage of the users at step 1, rounded to 1 decimal).

Sort by step_no.

The order of the rows matters for this question.

funnel
conversion
conditional aggregation
cte
union all
percentage

Read first: Lesson 49, SQL patterns for time: streaks, sessions, cohorts, funnels and rolling windows

Tables

1 table

These are the example rows. Run uses only these rows. Submit also tests your query on a second set of rows that you cannot see.

events4 columns · 28 rowsnot run yet

CREATE TABLE events (id INTEGER PRIMARY KEY, user_id INTEGER NOT NULL, event TEXT NOT NULL, event_time TEXT NOT NULL);

Example rows of the events table
id	user_id	event	event_time
1	1	view	2026-04-01 10:00:00
2	1	add_to_cart	2026-04-01 10:05:00
3	1	purchase	2026-04-01 10:20:00
4	2	view	2026-04-01 11:00:00
5	2	view	2026-04-01 11:30:00
6	2	add_to_cart	2026-04-01 11:40:00
7	3	view	2026-04-02 09:00:00
8	4	add_to_cart	2026-04-02 09:00:00
9	4	view	2026-04-02 09:30:00
10	4	purchase	2026-04-02 09:45:00
11	5	purchase	2026-04-02 14:00:00
12	7	view	2026-04-03 08:00:00
13	7	add_to_cart	2026-04-03 08:10:00
14	7	purchase	2026-04-03 08:30:00
15	7	purchase	2026-04-03 08:35:00
16	8	view	2026-04-03 10:00:00
17	8	view	2026-04-03 10:05:00
18	8	view	2026-04-03 10:10:00
19	9	view	2026-04-04 10:00:00
20	9	purchase	2026-04-04 10:10:00
21	9	add_to_cart	2026-04-04 10:20:00
22	10	view	2026-04-04 22:00:00
23	10	add_to_cart	2026-04-05 07:00:00
24	10	purchase	2026-04-05 07:15:00
25	11	add_to_cart	2026-04-05 09:00:00
26	11	view	2026-04-05 09:20:00
27	11	add_to_cart	2026-04-05 09:30:00
28	11	purchase	2026-04-05 09:40:00

About this SQL

A comparison with NULL (for example carted > viewed when the user never added to the cart) is NULL, which counts as false in CASE and WHERE. SQLite has no PIVOT and no FILTER before 3.30, so MIN(CASE WHEN ...) per action is the portable way to get "the first time of each action". UNION ALL stacks result rows, and ORDER BY at the end sorts the whole result.

SQLite

Opening the code editor…

Result

Run shows what your query gives on the example rows.Submit checks it on the example rows and on a hidden set of rows.

SQL · ready