Session-Level Funnel

Write a query to return the view → add_to_cart → purchase funnel from fn_session_events, counted by session rather than by user.

Start with each user-session pair’s first view. Its next step is the first add-to-cart strictly later than that view, and its purchase is the first purchase strictly later than that cart. All steps must belong to the same session. Equal timestamps do not count as later steps.

Return the three ordered steps as step, step_name and sessions. Count a session once per reached step; separate sessions from one user count separately. pct_of_previous is the session count divided by the preceding step’s count, multiplied by 100 and rounded to one decimal place. It is NULL for the first step or a zero preceding count.

Ordering: Sort by step ascending.

Returnstepstep_namesessionspct_of_previous

Columns

fn_session_events5 columns

columntype

event_idBIGINT

user_idVARCHAR

session_idVARCHAR

event_typeVARCHAR

event_atTIMESTAMP

Sample data & expected output

18 rows · 5 columns

event_iduser_idsession_idevent_typeevent_at

1f01s1view2024-06-01 10:00:00

2f01s1add_to_cart2024-06-01 10:05:00

3f01s1purchase2024-06-01 10:20:00

4f02s2view2024-06-01 11:00:00

5f02s3add_to_cart2024-06-02 09:00:00

6f02s3purchase2024-06-02 09:30:00

7f03s4view2024-06-01 12:00:00

8f03s4add_to_cart2024-06-01 12:10:00

9f04s5add_to_cart2024-06-01 13:00:00

10f04s5purchase2024-06-01 13:10:00

11f05s6view2024-06-01 14:00:00

12f05s6purchase2024-06-01 13:50:00

13f01s7view2024-06-01 15:00:00

14f01s7add_to_cart2024-06-01 15:05:00

15f01s7purchase2024-06-01 15:10:00

16f06s8view2024-06-01 16:00:00

17f06s8add_to_cart2024-06-01 16:00:00

18f06s8purchase2024-06-01 16:05:00

If you get stuck

Show hint · 3 hints Walkthrough Reveal answer

About this problem

You practise an ordered funnel at a grain other than the user. The chain is the familiar one – view, then a cart after it, then a purchase after that – but every link is keyed by the session as well as the user, and the counts are sessions. The skill is holding one grain through four CTEs without letting it drift.

Grain drift is quiet and it happens in two places. The first is the join: it is easy to group the output by user and session while joining on the user alone, which silently lets a later session’s cart continue an earlier session’s view. In this sample that turns three cart sessions into four, and nothing about the result looks wrong. The second is the final count: four careful session-keyed CTEs end in count(DISTINCT user_id) and the whole point is undone, with f01’s two sessions counted as one. Both mistakes read as reasonable SQL and both produce a plausible funnel. The other thing this problem makes you settle is the boundary. f06’s view and add-to-cart share a timestamp to the second, which happens constantly in real event data when two events are emitted by the same page load, and > and >= differ on exactly those rows. Neither is more correct in general; the business definition decides, and the prompt states it so there is something to check against.

Read the lesson: Funnels & Conversion (P22) →

Your query · runs in your browser⌘ Enter to run

Loading editor…

Output · run to see rows