Funnel conversion in order

SQL data engineering interview problem. Difficulty: advanced. Pattern: Funnel. About 22 minutes. Part of the Pro drill bank.

A shop tracks how far users go through its buying steps: view a product, add it to the cart, check out, and purchase. This is called a funnel. The key rule is the order: a user only reaches a step if they did the earlier steps first, each strictly after the one before. From funnel_events, return 1 row for each step with step_no (1 to 4), step, users (the number of distinct users who reached that step in order) and conversion_from_previous (users divided by the users of the previous step, rounded to 2 decimals, and NULL for step 1). Sort by step_no.

Requirements

  • A user reaches 'cart' only if they did a view and then a cart event strictly after it.
  • A user reaches 'checkout' only after reaching cart, with a checkout event after that cart.
  • A user reaches 'purchase' only after reaching checkout, with a purchase after that.
  • Count each user once per step.
  • conversion_from_previous is the users of this step divided by the users of the previous step, rounded to 2 decimals.
  • Sort by step_no.

Constraints

  • The table is funnel_events with the columns user_id, step and event_ts.
  • step is one of view, cart, checkout or purchase.
  • Build the funnel in stages: take the earliest view, then the earliest cart after it, then the earliest checkout after that, and so on.

Examples

Input: funnel_events user_id | step | event_ts u1 | view | 2024-04-01 09:00:00 u1 | cart | 2024-04-01 09:05:00 u1 | checkout | 2024-04-01 09:10:00 u1 | purchase | 2024-04-01 09:20:00 u2 | view | 2024-04-01 10:00:00 u2 | cart | 2024-04-01 10:02:00 u2 | checkout | 2024-04-01 10:04:00 u3 | view | 2024-04-01 11:00:00 u3 | cart | 2024-04-01 11:01:00 u4 | view | 2024-04-01 12:00:00 u4 | view | 2024-04-01 12:30:00 u5 | cart | 2024-04-01 08:00:00 ... Output: step_no | step | users | conversion_from_previous 1 | view | 8 | NULL 2 | cart | 6 | 0.75 3 | checkout | 3 | 0.5 4 | purchase | 2 | 0.67 Why this passes: All 8 users viewed. 6 carted after viewing (u4 never carted, u5 carted first). 3 checked out after carting (u1, u2, u7). 2 purchased after checking out (u1, u7).

Input: funnel_events user_id | step | event_ts a | view | 2024-04-01 09:00:00 a | cart | 2024-04-01 09:05:00 b | view | 2024-04-01 10:00:00 Output: step_no | step | users | conversion_from_previous 1 | view | 2 | NULL 2 | cart | 1 | 0.5 3 | checkout | 0 | 0 4 | purchase | 0 | NaN Why this passes: 2 users view, one carts after viewing.

Topics: lakebench, sql, funnel, ordered events, conversion.

More SQL interview questions · All interview problems · Learn data engineering

advanced

Funnel conversion in order

Interview-style drill: Count users who reach view, cart, checkout and purchase in that order, with step-to-step conversion.

`funnel_events` records `view`, `cart`, `checkout` and `purchase` events per user. A user reaches a step only if they performed the previous steps first, each strictly after the one before: view, then cart after that view, then checkout after that cart, then purchase after that checkout. Events out of order or steps skipped do not count. Return 1 row per step: - `step_no` (1 to 4) and `step` - `users`: number of distinct users who reached that step in order - `conversion_from_previous`: users / users at the previous step, rounded to 2 decimals (NULL for step 1) Order by `step_no`.