Abandoned Cart
ASKED IN INTERVIEWEtsy
Problem Statement
Etsy sellers want to re-engage customers who show interest in a product but do not complete the purchase. An "Abandoned Cart" item is defined as a product added to a cart that was not purchased by that same user within 48 hours of the addition.
Write an SQL query to list users and the products they abandoned.
Rules:
- Only consider items added to the cart at least 48 hours ago (Reference Date: 2026-02-11).
- An item is abandoned if no order for that product_id was placed by the user_id within the interval [added_at, added_at + 48 hours].
- Output columns: user_id, product_id, added_at.
- Sort the results by added_at in descending order.
Table Schema:
- CartAdditions: addition_id, user_id, product_id, added_at.
- Orders: order_id, user_id, product_id, order_placed_at.
Tests your understanding of
Joins, DateTime, Subqueries and Filtering
Input Tables
CartAdditions
| addition_id(INTEGER) | user_id(INTEGER) | product_id(INTEGER) | added_at(TIMESTAMP) |
|---|---|---|---|
| 1 | 1 | 501 | 2026-02-01 10:00:00 |
| 2 | 1 | 502 | 2026-02-08 12:00:00 |
| 3 | 2 | 501 | 2026-02-09 09:00:00 |
| 4 | 3 | 701 | 2026-02-05 15:00:00 |
| 5 | 4 | 888 | 2026-02-10 10:00:00 |
| 6 | 5 | 999 | 2026-02-01 08:00:00 |
Orders
| order_id(INTEGER) | user_id(INTEGER) | product_id(INTEGER) | order_placed_at(TIMESTAMP) |
|---|---|---|---|
| 1001 | 1 | 501 | 2026-02-01 10:30:00 |
| 1002 | 5 | 999 | 2026-02-04 08:00:00 |
Expected Output
| user_id(INTEGER) | product_id(INTEGER) | added_at(TIMESTAMP) |
|---|---|---|
| 1 | 502 | 2026-02-08 12:00:00 |
| 3 | 701 | 2026-02-05 15:00:00 |
| 5 | 999 | 2026-02-01 08:00:00 |
Tags
MediumASKED IN INTERVIEWJoinsDateTimeSubqueriesFiltering
15-20 min
45%