Onboarding Funnel With Steps Taken in Order

Hard
417.

Onboarding Funnel With Steps Taken in Order

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

ColumnType
user_idint
signed_up_atdatetime

Primary key: user_id.

One row per signup; signing up is step 1.

Table: events

ColumnType
event_idint
user_idint
event_namevarchar
occurred_atdatetime

Primary key: event_id.

Product events; other event names exist and do not matter. user_id always names a row of users.

Example

The rows must come back in the order shown.

users

user_idsigned_up_at
12024-08-01 09:00:00
22024-08-01 10:00:00
32024-08-02 11:00:00
42024-08-02 12:00:00
52024-08-03 08:00:00

events

event_iduser_idevent_nameoccurred_at
11project_created2024-08-01 09:30:00
21teammate_invited2024-08-01 09:45:00
31plan_upgraded2024-08-05 10:00:00
42teammate_invited2024-08-01 10:10:00
52project_created2024-08-01 10:30:00
63project_created2024-08-02 11:20:00
73plan_upgraded2024-08-03 09:00:00
84project_created2024-08-02 12:05:00
94teammate_invited2024-08-02 12:30:00
105login2024-08-03 08:01:00

Output

step_nostep_nameuserspct_of_previous
1signed_up5NULL
2project_created480
3teammate_invited250
4plan_upgraded150
Read the hints, the editorial and the reference solution
-- Write your MySQL query below

Sign in to write, run and submit queries

A free account — no card, no trial clock. You come straight back here.

Run your query to see its result next to the expected one, example by example.

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

ColumnType
user_idint
signed_up_atdatetime

Primary key: user_id.

One row per signup; signing up is step 1.

Table: events

ColumnType
event_idint
user_idint
event_namevarchar
occurred_atdatetime

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_idsigned_up_at
12024-08-01 09:00:00
22024-08-01 10:00:00
32024-08-02 11:00:00
42024-08-02 12:00:00
52024-08-03 08:00:00

events

event_iduser_idevent_nameoccurred_at
11project_created2024-08-01 09:30:00
21teammate_invited2024-08-01 09:45:00
31plan_upgraded2024-08-05 10:00:00
42teammate_invited2024-08-01 10:10:00
52project_created2024-08-01 10:30:00
63project_created2024-08-02 11:20:00
73plan_upgraded2024-08-03 09:00:00
84project_created2024-08-02 12:05:00
94teammate_invited2024-08-02 12:30:00
105login2024-08-03 08:01:00

Output

step_nostep_nameuserspct_of_previous
1signed_up5NULL
2project_created480
3teammate_invited250
4plan_upgraded150

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_no

Another 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 →