Onboarding Funnel With Steps Taken in Order — SQL Problem
The onboarding funnel has four steps: 1 signed_up (every user), 2 project_created, 3 teammate_invited, 4 plan_upgraded.
- Difficulty: Hard
- Topics: Joins, Aggregation, Conditional Logic
- Dialect: MySQL
- Problem: #417
Problem statement
The onboarding funnel has four steps: 1 signed_up (every user), 2 project_created, 3 teammate_invited, 4 plan_upgraded. A user reaches a step only if they reached the step before it and fired the step's event at or after the moment they reached the previous step — the time they reach a step is the earliest such event. An invite sent before the user's first project does not count, and a user who upgrades without ever inviting stops at step 2.
Return all four steps with step_no, step_name, users (users who reached the step) and pct_of_previous = 100 × users ÷ the previous step's users, rounded to 1 decimal — NULL for step 1, and NULL when the previous step has no users. Order by step_no.
Tables
Table: users
| Column | Type |
|---|---|
| user_id | int |
| signed_up_at | datetime |
Primary key: user_id.
One row per signup; signing up is step 1.
Table: events
| Column | Type |
|---|---|
| event_id | int |
| user_id | int |
| event_name | varchar |
| occurred_at | datetime |
Primary key: event_id.
Product events; other event names exist and do not matter. user_id always names a row of users.
Examples
Example 1
users
| user_id | signed_up_at |
|---|---|
| 1 | 2024-08-01 09:00:00 |
| 2 | 2024-08-01 10:00:00 |
| 3 | 2024-08-02 11:00:00 |
| 4 | 2024-08-02 12:00:00 |
| 5 | 2024-08-03 08:00:00 |
events
| event_id | user_id | event_name | occurred_at |
|---|---|---|---|
| 1 | 1 | project_created | 2024-08-01 09:30:00 |
| 2 | 1 | teammate_invited | 2024-08-01 09:45:00 |
| 3 | 1 | plan_upgraded | 2024-08-05 10:00:00 |
| 4 | 2 | teammate_invited | 2024-08-01 10:10:00 |
| 5 | 2 | project_created | 2024-08-01 10:30:00 |
| 6 | 3 | project_created | 2024-08-02 11:20:00 |
| 7 | 3 | plan_upgraded | 2024-08-03 09:00:00 |
| 8 | 4 | project_created | 2024-08-02 12:05:00 |
| 9 | 4 | teammate_invited | 2024-08-02 12:30:00 |
| 10 | 5 | login | 2024-08-03 08:01:00 |
Output
| step_no | step_name | users | pct_of_previous |
|---|---|---|---|
| 1 | signed_up | 5 | NULL |
| 2 | project_created | 4 | 80 |
| 3 | teammate_invited | 2 | 50 |
| 4 | plan_upgraded | 1 | 50 |
How to solve Onboarding Funnel With Steps Taken in Order
An ordered funnel is a chain: whether a user reaches step k depends on when they reached step k − 1. So compute a reach time per user per step, each from the previous. Step 2 joins every user to their project_created events at or after signup and takes the MIN; step 3 starts from step 2's users only and joins teammate_invited events at or after that step-2 time; step 4 likewise. An inner join at each link drops users who never take the step — and anyone dropped can never come back at a later step.
The time conditions are what make it an ordered funnel. User 2 invited a teammate at 10:10 but created their first project at 10:30, so the invite does not count; user 3 upgraded without inviting and stops at step 2. Counting events independently per step would credit both.
Each CTE's row count is a step's size. UNION ALL stacks them as rows, and LAG(users) OVER (ORDER BY step_no) brings the previous step's count beside each row: NULL on step 1, and NULLIF(…, 0) turns a zero previous step into NULL instead of a division error. The rate is rounded to one decimal so MySQL and other engines agree.
The alternative computes the three reach times per user with correlated MIN subqueries (a NULL time propagates — occurred_at >= NULL is never true), counts them in one row, and writes each percentage explicitly. With an index on events(user_id, event_name, occurred_at) every step is a seek per user.
Reference solution (MySQL)
WITH s2 AS (
SELECT u.user_id, MIN(e.occurred_at) AS reached_at
FROM users u JOIN events e
ON e.user_id = u.user_id AND e.event_name = 'project_created' AND e.occurred_at >= u.signed_up_at
GROUP BY u.user_id
), s3 AS (
SELECT s.user_id, MIN(e.occurred_at) AS reached_at
FROM s2 s JOIN events e
ON e.user_id = s.user_id AND e.event_name = 'teammate_invited' AND e.occurred_at >= s.reached_at
GROUP BY s.user_id
), s4 AS (
SELECT s.user_id, MIN(e.occurred_at) AS reached_at
FROM s3 s JOIN events e
ON e.user_id = s.user_id AND e.event_name = 'plan_upgraded' AND e.occurred_at >= s.reached_at
GROUP BY s.user_id
), steps AS (
SELECT 1 AS step_no, 'signed_up' AS step_name, COUNT(*) AS users FROM users
UNION ALL SELECT 2, 'project_created', COUNT(*) FROM s2
UNION ALL SELECT 3, 'teammate_invited', COUNT(*) FROM s3
UNION ALL SELECT 4, 'plan_upgraded', COUNT(*) FROM s4
)
SELECT step_no, step_name, users,
ROUND(100 * users / NULLIF(LAG(users) OVER (ORDER BY step_no), 0), 1) AS pct_of_previous
FROM steps
ORDER BY step_noAnother way
WITH p2 AS (
SELECT u.user_id, (SELECT MIN(e.occurred_at) FROM events e WHERE e.user_id = u.user_id AND e.event_name = 'project_created' AND e.occurred_at >= u.signed_up_at) AS t2
FROM users u
), p3 AS (
SELECT p.user_id, p.t2, (SELECT MIN(e.occurred_at) FROM events e WHERE e.user_id = p.user_id AND e.event_name = 'teammate_invited' AND e.occurred_at >= p.t2) AS t3
FROM p2 p
), p4 AS (
SELECT p.user_id, p.t2, p.t3, (SELECT MIN(e.occurred_at) FROM events e WHERE e.user_id = p.user_id AND e.event_name = 'plan_upgraded' AND e.occurred_at >= p.t3) AS t4
FROM p3 p
), r AS (
SELECT COUNT(*) AS c1, COUNT(t2) AS c2, COUNT(t3) AS c3, COUNT(t4) AS c4 FROM p4
)
SELECT 1 AS step_no, 'signed_up' AS step_name, c1 AS users, NULL AS pct_of_previous FROM r
UNION ALL SELECT 2, 'project_created', c2, ROUND(100 * c2 / NULLIF(c1, 0), 1) FROM r
UNION ALL SELECT 3, 'teammate_invited', c3, ROUND(100 * c3 / NULLIF(c2, 0), 1) FROM r
UNION ALL SELECT 4, 'plan_upgraded', c4, ROUND(100 * c4 / NULLIF(c3, 0), 1) FROM r
ORDER BY step_no← User Sessions From Thirty-Minute Event Gaps · Monthly Signup Cohort Retention →