The Abandoned Carts
Cart abandonment is the moment a shopper puts something in their basket before leaving without paying, and it is the cheapest problem in e-commerce to attack: the person has already chosen the item. Kindled wants the reminder email to go to exactly those people — anybody who reached the basket and never bought. Somebody who did buy must not be reminded of a purchase they already made, and the email tool takes id numbers rather than names.
events — one row per tracked action. user_id points at a row in users. event_type is one of signup, view_product, add_to_cart and purchase. event_date is the day the action happened. Nothing guarantees a person produces all four kinds, or any particular number of rows.
| id | user_id | event_type | event_date |
|---|---|---|---|
| 1 | 1 | signup | 2023-05-01 |
| 2 | 1 | view_product | 2023-05-01 |
| 3 | 1 | add_to_cart | 2023-05-02 |
| 4 | 1 | purchase | 2023-05-03 |
| 5 | 2 | signup | 2023-05-02 |
| 6 | 2 | view_product | 2023-05-02 |
| 7 | 3 | signup | 2023-05-03 |
| 8 | 3 | view_product | 2023-05-03 |
| 9 | 3 | add_to_cart | 2023-05-04 |
| 10 | 4 | signup | 2023-05-04 |
| 11 | 4 | view_product | 2023-05-05 |
| 12 | 4 | add_to_cart | 2023-05-05 |
| 13 | 4 | purchase | 2023-05-06 |
| 14 | 5 | signup | 2023-05-05 |
| 15 | 6 | signup | 2023-05-06 |
| 16 | 6 | view_product | 2023-05-06 |
| 17 | 7 | signup | 2023-05-07 |
| 18 | 7 | view_product | 2023-05-07 |
| 19 | 7 | add_to_cart | 2023-05-08 |
| 20 | 7 | purchase | 2023-05-09 |
| 21 | 8 | signup | 2023-05-08 |
The table already exists in the database — there is nothing to create or load.
Task: Write a query that returns a single column, user_id, listing everybody who has put something in a basket but has never recorded a purchase. Each person appears exactly once, in ascending user_id order.
Example output — shape only, on invented people. If two baskets had been left behind, you would get:
| user_id |
|---|
| 24 |
| 29 |
Sign in to solve this problem
Reading problems is free for everyone — solving them (Run, Submit, and tracking what you've solved) needs an account.
Sign in