task Funnels & conversion · hard
Find a truly ordered cart funnel
Find every shopper/cart pair with a view followed by checkout followed by purchase, all for that cart, where purchase occurs within 30 minutes of the view. Return each qualifying pair once.
what to return
- Columns
shopper_idcart_idin this order- Rows
- 5 rows · in the order asked
- Watch for
- Deduplicate globally by event_key, keeping the smallest id; a retry may have a conflicting payload.
- Use (occurred_at, id) as the total event order; equal timestamps follow id order. All times are UTC.
- The 30-minute limit is inclusive. No session boundary rule is used for this question. Order by shopper_id, cart_id.
- Skill
- Distinguish an ordered funnel from the mere presence of three event types. Refresher: Sessionize an event stream →
expected result
Your query should return exactly these columns and values. Run it and compare.
expected result5 rows · in this order
| shopper_id | cart_id |
|---|---|
| 1 | A |
| 2 | C |
| 4 | E |
first 3 of 5 rows
data you can query
Sample rows from each table. Each column shows its type underneath; → marks a link to another table.
market_web_events7 columns · 38 rows
| idinteger · key | event_keytext | shopper_idinteger → shoppers | occurred_attext | event_typetext | channeltext | cart_idtext |
|---|---|---|---|---|---|---|
| 1 | a1 | 1 | 2025-05-01 09:00:00 | view | A | |
| 2 | a2 | 1 | 2025-05-01 09:05:00 | checkout | direct | A |
| 3 | a3 | 1 | 2025-05-01 09:10:00 | purchase | direct | A |
| 4 | a3 | 1 | 2025-05-01 09:10:00 | purchase | direct | A |
| 5 | b1 | 1 | 2025-05-01 10:00:00 | view | search | B |
showing 5 of 38 rows
solution.sql
Hints and the answer are always available. Using them counts the question as solved with help.