task Funnels & conversion · medium
Identify abandoned checkout attempts
At the fixed observation cutoff 2025-05-03 00:00:00, list each canonical checkout that had no later purchase of the same cart within 30 minutes. Return checkout_id, shopper_id, cart_id and its deadline.
what to return
- Columns
checkout_idshopper_idcart_iddeadlinein this order- Rows
- 7 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.
- All provided checkout deadlines are before the observation cutoff. A purchase at the deadline counts as timely. Different checkout keys are separate attempts even if timestamps match. Order by checkout_id.
- Skill
- Find checkout attempts without a timely later purchase of the same cart. Refresher: Sessionize an event stream →
expected result
Your query should return exactly these columns and values. Run it and compare.
expected result7 rows · in this order
| checkout_id | shopper_id | cart_id | deadline |
|---|---|---|---|
| 6 | 1 | B | 2025-05-01 10:35:00 |
| 19 | 5 | F | 2025-05-01 09:35:00 |
| 22 | 6 | G | 2025-05-01 09:40:00 |
first 3 of 7 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.