Amazon: Return Requests With No Delivered Order Behind Them

Easy
577.

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.

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

ColumnTypeValues
order_idint
customer_idint
ordered_ondate
order_statusenumplaced, shipped, delivered, cancelled

Primary key: order_id.

One row per customer order with its current status.

Table: ReturnRequest

ColumnTypeValues
return_idint
order_idint
requested_ondate
reasonenumdamaged, 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.

Example

The rows may come back in any order; the columns must be the ones shown, in that order.

CustomerOrder

order_idcustomer_idordered_onorder_status
9001412025-04-01delivered
9002422025-04-02shipped
9003432025-04-02cancelled
9004412025-04-03delivered

ReturnRequest

return_idorder_idrequested_onreason
190012025-04-06damaged
290022025-04-05not_needed
390102025-04-07wrong_item
490032025-04-08defective
590042025-04-09not_needed

Output

return_idorder_idproblem
29002not delivered
39010missing order
49003not delivered
Read the hints, the editorial and the reference solution
-- Write your MySQL query below

Sign in to write, run and submit queries

A free account — no card, no trial clock. You come straight back here.

Run your query to see its result next to the expected one, example by example.

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

ColumnType
order_idint
customer_idint
ordered_ondate
order_statusenum(placed, shipped, delivered, cancelled)

Primary key: order_id.

One row per customer order with its current status.

Table: ReturnRequest

ColumnType
return_idint
order_idint
requested_ondate
reasonenum(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_idcustomer_idordered_onorder_status
9001412025-04-01delivered
9002422025-04-02shipped
9003432025-04-02cancelled
9004412025-04-03delivered

ReturnRequest

return_idorder_idrequested_onreason
190012025-04-06damaged
290022025-04-05not_needed
390102025-04-07wrong_item
490032025-04-08defective
590042025-04-09not_needed

Output

return_idorder_idproblem
29002not delivered
39010missing order
49003not 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 →