Advanced
Case 41 of 50
Case 041: The Cross-Sell Opportunity
customersordersorder_itemsproducts
The Audio team is planning an upsell push and wants a list of people worth targeting — not random customers, people already primed to buy.
The manager passes along their ask: "Could you find everyone who's bought Accessories from us but never once bought from Audio? Those are our best upsell targets."
The data Alex is looking at
customers
| id | name | country | signup_date |
|---|---|---|---|
| 1 | Sam | Germany | 2026-01-02 |
| 2 | Nina | Canada | 2026-01-05 |
| 3 | David | Germany | 2026-01-10 |
| 4 | Maya | Japan | 2026-01-15 |
| 5 | Daniel | Canada | 2026-01-20 |
| … 5 more rows | |||
orders
| id | customer_id | order_date |
|---|---|---|
| 101 | 1 | 2026-01-05 |
| 102 | 2 | 2026-01-08 |
| 103 | 3 | 2026-01-12 |
| 104 | 1 | 2026-01-20 |
| 105 | 4 | 2026-02-01 |
| … 23 more rows | ||
order_items
| order_id | product_id | quantity |
|---|---|---|
| 101 | 1 | 2 |
| 101 | 2 | 1 |
| 102 | 3 | 1 |
| 103 | 2 | 3 |
| 104 | 5 | 1 |
| … 29 more rows | ||
products
| id | name | category | price |
|---|---|---|---|
| 1 | Keyboard | Accessories | 2500 |
| 2 | Mouse | Accessories | 1200 |
| 3 | Headphones | Audio | 3500 |
| 4 | Monitor | Displays | 12000 |
| 5 | Laptop | Computers | 60000 |
| … 5 more rows | |||
TASK
Help Alex answer the manager:
“Find every customer who has bought from the 'Accessories' category but has never bought from the 'Audio' category.”
Write a query above and hit Run to see what comes back.
No hints requested yet — click "Get a hint" when you're stuck.
Alex's final query — this is for reference. Copy it into your own thinking, not into the editor above.
SELECT customers.name
FROM customers
WHERE customers.id IN (
SELECT orders.customer_id
FROM orders
JOIN order_items ON order_items.order_id = orders.id
JOIN products ON products.id = order_items.product_id
WHERE products.category = 'Accessories'
EXCEPT
SELECT orders.customer_id
FROM orders
JOIN order_items ON order_items.order_id = orders.id
JOIN products ON products.id = order_items.product_id
WHERE products.category = 'Audio'
);
What that query returns
Case Debrief
EXCEPT (set difference)
- EXCEPT is the set-difference operator: first-query-only, nothing from the second — exactly the shape of "bought this, but never that."