Scenario: Streamhub's growth team is measuring conversion from initial product exploration to upgrade intent.
Task: Write a query to return two counts: the number of users who performed at least one 'page_view' event (reached_step_1), and the number of those users who later performed at least one 'upgrade_clicked' event after their first 'page_view' (reached_step_2).
Assumptions:
- A user reaches step 1 by performing at least one
'page_view'event at any time. - A user reaches step 2 by performing at least one
'upgrade_clicked'event strictly after their earliest'page_view'. - Every user who reaches step 2 also reaches step 1.
Output:
- One row, holding the two step counts.
- Columns in this order:
reached_step_1,reached_step_2.
Schema · analytics 5 tables
Run previews · Check grades
Write a query, then run it to see results here.
Worked solution Try it yourself first
WITH
step_1 AS (
SELECT
user_id,
MIN(occurred_at) AS step_1_time
FROM
events
WHERE
event_type = 'page_view'
GROUP BY
user_id
),
upgrade_events AS (
SELECT
user_id,
occurred_at
FROM
events
WHERE
event_type = 'upgrade_clicked'
),
step_2 AS (
SELECT
ue.user_id,
MIN(ue.occurred_at) AS step_2_time
FROM
upgrade_events ue
JOIN step_1 s ON s.user_id = ue.user_id
WHERE
(ue.occurred_at - s.step_1_time) > INTERVAL '0 seconds'
GROUP BY
ue.user_id
)
SELECT
(
SELECT
COUNT(*)
FROM
step_1
) AS reached_step_1,
COUNT(*) AS reached_step_2
FROM
step_2The shape
A two-step funnel is two stacked CTEs: the first finds each user's earliest qualifying event for step 1, the second restricts to users who reached step 1 and finds their earliest step-2 event that happens strictly after their step-1 time. Counting users in each CTE gives the conversion numbers.
Clause by clause
step_1selectsuser_id, MIN(occurred_at) AS step_1_time FROM events WHERE event_type = 'page_view' GROUP BY user_id. Filtering before aggregating meansMINruns only over the qualifying event type, so each user's step-1 time is their earliest'page_view'.upgrade_eventsis a thin staging CTE that pre-filterseventsdown to the rows whereevent_type = 'upgrade_clicked', which keeps the next join small.step_2joinsupgrade_eventstostep_1onuser_idso only users who reached step 1 are considered, then filters with(ue.occurred_at - s.step_1_time) > INTERVAL '0 seconds'to keep only upgrade clicks that happened strictly after the user's first'page_view'.MIN(ue.occurred_at)per user then picks each user's earliest qualifying upgrade.- The final
SELECT (SELECT COUNT(*) FROM step_1) AS reached_step_1, COUNT(*) AS reached_step_2 FROM step_2uses a scalar subquery for the step-1 count and counts rows ofstep_2for step 2. Both CTEs are already one row per user, so a plainCOUNT(*)is the per-user count.
The trap
The time-ordering constraint is the difference between a real funnel and a count of who ever did each event. Dropping (ue.occurred_at - s.step_1_time) > INTERVAL '0 seconds' would let a user who clicked upgrade before they ever viewed a page count as converted, which inflates step 2 with backwards journeys. The strict greater-than is what makes it a funnel.
You practiced staging a two-step funnel as chained CTEs — earliest qualifying event per user at step 1, then qualifying events at step 2 that occur after each user's step-1 timestamp.
