Amazon: Return Requests With No Delivered Order Behind Them
The returns-abuse team audits return requests that should not exist: a return can only be raised against an order that was delivered.
- Difficulty: Easy
- Topics: Joins, Conditional Logic
- Dialect: MySQL
- Problem: #577
Problem statement
The returns-abuse team audits return requests that should not exist: a return can only be raised against an order that was delivered.
Return every return request whose order is missing from CustomerOrder, or exists but is not delivered. Return return_id, order_id and problem, which is 'missing order' when no order with that id exists and 'not delivered' otherwise. Return the rows in any order.
A practice scenario set at Amazon: the tables and data are invented, and this is not a question from, or endorsed by, Amazon.
Tables
Table: CustomerOrder
| Column | Type |
|---|---|
| order_id | int |
| customer_id | int |
| ordered_on | date |
| order_status | enum(placed, shipped, delivered, cancelled) |
Primary key: order_id.
One row per customer order with its current status.
Table: ReturnRequest
| Column | Type |
|---|---|
| return_id | int |
| order_id | int |
| requested_on | date |
| reason | enum(damaged, wrong_item, not_needed, defective) |
Primary key: return_id.
One row per return request. order_id is never NULL, but it may name an order that does not exist.
Examples
Example 1
CustomerOrder
| order_id | customer_id | ordered_on | order_status |
|---|---|---|---|
| 9001 | 41 | 2025-04-01 | delivered |
| 9002 | 42 | 2025-04-02 | shipped |
| 9003 | 43 | 2025-04-02 | cancelled |
| 9004 | 41 | 2025-04-03 | delivered |
ReturnRequest
| return_id | order_id | requested_on | reason |
|---|---|---|---|
| 1 | 9001 | 2025-04-06 | damaged |
| 2 | 9002 | 2025-04-05 | not_needed |
| 3 | 9010 | 2025-04-07 | wrong_item |
| 4 | 9003 | 2025-04-08 | defective |
| 5 | 9004 | 2025-04-09 | not_needed |
Output
| return_id | order_id | problem |
|---|---|---|
| 2 | 9002 | not delivered |
| 3 | 9010 | missing order |
| 4 | 9003 | not delivered |
How to solve Amazon: Return Requests With No Delivered Order Behind Them
Two kinds of bad request are wanted, and one of them — an order id that matches nothing — would vanish in an inner join. A LEFT JOIN from ReturnRequest to CustomerOrder keeps every request; where no order matched, the order columns are NULL.
The filter then keeps a row if o.order_id IS NULL (missing) or the matched order's status is anything but delivered. Note that the NULL check must come first in the reasoning: for a missing order o.order_status <> 'delivered' is NULL, not true, so without the explicit IS NULL those requests would be lost. The same test drives the CASE that writes the label.
The alternative splits the two cases: an anti join with NOT EXISTS for the missing orders and an inner join for the undelivered ones, glued with UNION ALL (the two sets cannot overlap, so no duplicates arise). Both read each table once with an index on order_id.
Reference solution (MySQL)
SELECT r.return_id, r.order_id,
CASE WHEN o.order_id IS NULL THEN 'missing order' ELSE 'not delivered' END AS problem
FROM ReturnRequest r
LEFT JOIN CustomerOrder o ON o.order_id = r.order_id
WHERE o.order_id IS NULL OR o.order_status <> 'delivered'Another way
SELECT return_id, order_id, 'missing order' AS problem FROM ReturnRequest r
WHERE NOT EXISTS (SELECT 1 FROM CustomerOrder o WHERE o.order_id = r.order_id)
UNION ALL
SELECT r.return_id, r.order_id, 'not delivered' FROM ReturnRequest r JOIN CustomerOrder o ON o.order_id = r.order_id WHERE o.order_status <> 'delivered'← Amazon: Associates Meeting the 100 Units-per-Hour Pick Target · Amazon: Alexa Requests and Households by Device Family →