task IN / EXISTS / NOT EXISTS · medium
Loyal shoppers who never needed a refund
Return the id and name of shoppers who have at least two delivered orders and have never received a refund on any of their orders.
what to return
- Columns
idnamein this order- Rows
- 2 rows · any order
- Watch for
- A refund on any order — delivered or not — disqualifies the shopper.
- Skill
- Combine “at least N” with “never” conditions. Refresher: Filter groups with HAVING →
expected result
Your query should return exactly these columns and values. Run it and compare.
expected result2 rows · any order
| id | name |
|---|---|
| 5 | Esi Mensah |
| 6 | Farah Aziz |
all 2 rows
data you can query
Sample rows from each table. Each column shows its type underneath; → marks a link to another table.
shoppers5 columns · 17 rows
| idinteger · key | nametext | emailtext | regiontext | signup_datetext |
|---|---|---|---|---|
| 1 | Ana Ruiz | ana@harbor.test | West | 2025-01-03 |
| 2 | Ben Okafor | ben@harbor.test | East | 2025-01-05 |
| 3 | Chloe Park | chloe@harbor.test | West | 2025-01-09 |
| 4 | Dmitri Volkov | dmitri@harbor.test | North | 2025-01-14 |
| 5 | Esi Mensah | NULL | South | 2025-01-20 |
showing 5 of 17 rows
sales_orders5 columns · 33 rows
| idinteger · key | shopper_idinteger → shoppers | order_datetext | statustext | delivered_datetext |
|---|---|---|---|---|
| 101 | 1 | 2025-01-04 | delivered | 2025-01-08 |
| 102 | 2 | 2025-01-06 | delivered | 2025-01-07 |
| 103 | 1 | 2025-01-15 | delivered | 2025-01-20 |
| 104 | 3 | 2025-01-18 | cancelled | NULL |
| 105 | 4 | 2025-01-22 | delivered | 2025-01-31 |
showing 5 of 33 rows
order_refunds4 columns · 6 rows
| idinteger · key | order_idinteger | refunded_attext | amountreal |
|---|---|---|---|
| 1 | 103 | 2025-01-25 | 15 |
| 2 | 106 | 2025-02-06 | 74 |
| 3 | 111 | 2025-02-25 | 59 |
| 4 | 111 | 2025-02-26 | 45 |
| 5 | 122 | 2025-03-20 | 10 |
showing 5 of 6 rows
solution.sql
Hints and the answer are always available. Using them counts the question as solved with help.