Sellers With a High Return Rate — SQL Joins Problem
The trust & safety team reviews sellers whose buyers send too much back.
- Difficulty: Medium
- Topics: Joins, Aggregation
- Dialect: MySQL
- Problem: #107
Problem statement
The trust & safety team reviews sellers whose buyers send too much back. A seller's return rate is the share of their delivered items that have a return request; items never delivered (delivered_on NULL) do not count at all.
Return the sellers with at least 4 delivered items and a return rate strictly above 25%, with the columns seller_id, delivered_items, returned_items and return_rate_pct (100 × returned ÷ delivered, rounded to 2 decimals). Order by return_rate_pct descending, then seller_id ascending.
Tables
Table: order_items
| Column | Type |
|---|---|
| item_id | int |
| order_id | int |
| seller_id | int |
| unit_price | int |
| delivered_on | date |
Primary key: item_id.
One row per item sold. delivered_on is NULL while the item is in transit or if it was cancelled.
Table: returns
| Column | Type |
|---|---|
| return_id | int |
| item_id | int |
| reason | enum(damaged, wrong_item, size_issue, not_as_described, changed_mind) |
| refund_amount | int |
Primary key: return_id.
At most one return request per item, always for a delivered item.
Examples
Example 1
order_items
| item_id | order_id | seller_id | unit_price | delivered_on |
|---|---|---|---|---|
| 1 | 701 | 10 | 999 | 2024-07-02 |
| 2 | 702 | 10 | 499 | 2024-07-03 |
| 3 | 703 | 10 | 1299 | 2024-07-03 |
| 4 | 704 | 10 | 799 | 2024-07-05 |
| 5 | 705 | 20 | 2499 | 2024-07-04 |
| 6 | 706 | 20 | 1999 | 2024-07-06 |
| 7 | 707 | 20 | 349 | 2024-07-06 |
| 8 | 708 | 20 | 599 | 2024-07-08 |
| 9 | 709 | 20 | 899 | NULL |
| 10 | 710 | 30 | 1599 | 2024-07-09 |
| 11 | 711 | 30 | 1599 | 2024-07-10 |
returns
| return_id | item_id | reason | refund_amount |
|---|---|---|---|
| 1 | 1 | damaged | 999 |
| 2 | 3 | size_issue | 1299 |
| 3 | 5 | wrong_item | 2499 |
| 4 | 10 | changed_mind | 1599 |
| 5 | 11 | damaged | 1599 |
Output
| seller_id | delivered_items | returned_items | return_rate_pct |
|---|---|---|---|
| 10 | 4 | 2 | 50 |
How to solve Sellers With a High Return Rate
The denominator is every delivered item of the seller and the numerator the ones with a return, so the natural shape is order_items filtered to delivered_on IS NOT NULL, LEFT JOINed to returns. Each item appears once (at most one return per item), with return_id NULL when nothing came back; COUNT(*) counts delivered items and COUNT(r.return_id) counts returned ones.
The two thresholds apply to groups, so they live in HAVING. "Strictly above 25%" is written without division: 4 * returned > delivered. That avoids any rounding question at the boundary — seller 20 in the example has 1 return out of 4 deliveries, exactly 25%, and is out. The rounded percentage is computed only for display.
The undelivered item of seller 20 is filtered out before the join, so it changes neither count. An inner join would be wrong here: it would keep only returned items and make every rate 100%. The alternative counts returns per item with an EXISTS inside a conditional SUM. Cost: one pass over the items with an index lookup into returns per delivered item.
Reference solution (MySQL)
SELECT i.seller_id,
COUNT(*) AS delivered_items,
COUNT(r.return_id) AS returned_items,
ROUND(100 * COUNT(r.return_id) / COUNT(*), 2) AS return_rate_pct
FROM order_items i
LEFT JOIN returns r ON r.item_id = i.item_id
WHERE i.delivered_on IS NOT NULL
GROUP BY i.seller_id
HAVING COUNT(*) >= 4 AND 4 * COUNT(r.return_id) > COUNT(*)
ORDER BY return_rate_pct DESC, i.seller_idAnother way
SELECT seller_id, delivered_items, returned_items, ROUND(100 * returned_items / delivered_items, 2) AS return_rate_pct
FROM (
SELECT seller_id, COUNT(*) AS delivered_items,
SUM(CASE WHEN EXISTS (SELECT 1 FROM returns r WHERE r.item_id = i.item_id) THEN 1 ELSE 0 END) AS returned_items
FROM order_items i WHERE delivered_on IS NOT NULL GROUP BY seller_id
) t
WHERE delivered_items >= 4 AND returned_items * 100 > delivered_items * 25
ORDER BY return_rate_pct DESC, seller_id← Cart Abandonment Rate by Device · Best-Rated Product in Each Category →