Strict Funnel: No Unrelated Events Between Steps

Write a query to return the three view → add_to_cart → purchase steps from fn_events, with distinct user counts under two definitions:

loose_users: Start from a user’s first view, then select the first cart at a strictly later timestamp and the first purchase strictly after that cart. Other events may occur between steps.

strict_users: Count users with the required steps as consecutive rows in their event sequence. For step 2, view must be immediately followed by cart; for step 3, that cart must be immediately followed by purchase. Any intervening event, including another view, breaks that attempt. A later valid consecutive attempt can still qualify the user.

Order each user’s events by event_at, then event_id, both ascending. Step 1 counts users with a view under both definitions. Events before a selected starting view do not break a strict attempt. Return one row per ordered step with step, step_name and both counts.

Ordering: Sort by step ascending.

Returnstepstep_nameloose_usersstrict_users

Columns

fn_events4 columns

columntype

event_idBIGINT

user_idVARCHAR

event_typeVARCHAR

event_atTIMESTAMP

Sample data & expected output

18 rows · 4 columns

event_iduser_idevent_typeevent_at

1f01view2024-06-01 10:00:00

2f01add_to_cart2024-06-01 10:05:00

3f01purchase2024-06-01 10:20:00

4f02view2024-06-01 11:00:00

5f02search2024-06-01 11:05:00

6f02add_to_cart2024-06-01 11:10:00

7f02purchase2024-06-01 11:20:00

8f03view2024-06-01 12:00:00

9f03add_to_cart2024-06-01 12:05:00

10f03support2024-06-01 12:10:00

11f03purchase2024-06-01 12:20:00

12f04view2024-06-01 13:00:00

13f04add_to_cart2024-06-01 13:05:00

14f06purchase2024-06-01 13:10:00

15f05search2024-06-01 14:00:00

16f05view2024-06-01 14:05:00

17f05add_to_cart2024-06-01 14:10:00

18f05purchase2024-06-01 14:15:00

If you get stuck

Show hint · 3 hints Walkthrough Reveal answer

About this problem

You practise two definitions of the same funnel in one query, and the work is in keeping them honest about different things. The loose funnel asks whether the steps happened in order. The strict funnel asks whether they happened back to back. Everything between the two columns – one user at step 2, two at step 3 – is users who did the right things in the right order with something else in the middle.

The strict half needs a different tool, and that is the lesson. Ordering and adjacency are not the same question, so no amount of care with > and min() will express “nothing in between”. Once you reach for a window function, PARTITION BY user_id becomes load-bearing in a way it was not before: without it the window runs off the end of one user and into the next, and f04’s incomplete chain is completed by f06’s unrelated purchase. That failure is silent, it inflates the strict count, and it is more likely in a real table than in this sample, because real tables have many users whose events interleave. The second temptation is to clean the stream first. Filtering to the three funnel event types makes the adjacency test easy and makes strict equal loose, which is the one answer you can be sure is wrong – the detours you removed were the thing being counted. The last thing to settle is where the funnel starts. f05 searched before viewing, and that is not a detour inside the funnel; it is what someone did before entering it. The rule has to be about the events between the steps, not about the user’s history, or every user who did anything before their first view is excluded.

Read the lesson: Funnels & Conversion (P22) →

Your query · runs in your browser⌘ Enter to run

Loading editor…

Output · run to see rows