SQL data engineering interview problem. Difficulty: advanced. Pattern: Funnel. About 22 minutes. Part of the Pro drill bank.
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 one 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.
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: Two users view, one carts after viewing.
Topics: lakebench, sql, funnel, ordered events, conversion.
More SQL interview questions · All interview problems · Learn data engineering
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 one 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`.