Write a query to return users in fn_events who have a purchase but no add_to_cart event strictly before their first purchase.
Return that earliest purchase as first_purchase_at, carts_before as the count of carts strictly before it, and carts_total as the count of all carts for the user, including later carts. Include only users whose carts_before is 0. Exclude users with no purchase.
Ordering: Sort by user_id ascending.
Returnuser_idfirst_purchase_atcarts_beforecarts_total
About this problem
You practise reading the funnel chain backwards. Instead of counting who made it from one step to the next, you find the users who arrived at a step without having passed through the one before, which is how out-of-order events and undocumented paths get noticed. The skill is that every condition here is about order rather than existence.
That distinction is the whole problem. f02 and f05 both have an add-to-cart event in their history, so any query that asks “did this user ever cart” clears them and reports only f03. What makes them interesting is when: f05’s cart is at the same second as the purchase, which looks like one checkout emitting two events, and f02’s is twenty minutes afterwards, which looks like a genuinely odd path or a clock problem. Both are worth a look, and both are invisible to a NOT IN over the cart users. The second column exists for the same reason: once you filter on carts_before = 0, three rows come back for three different reasons, and carts_total is the one number that tells them apart on the page. The boundary case deserves its own attention too – in a real event table, events sharing a timestamp to the second are common rather than exotic, so < against <= is a decision you will make repeatedly, and it is better made deliberately than by whichever operator you typed first.
Read the lesson: Funnels & Conversion (P22) →