Sellers With a High Return Rate

Medium
107.

Sellers With a High Return Rate

MediumJoinsAggregation

MySQL

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

ColumnType
item_idint
order_idint
seller_idint
unit_priceint
delivered_ondate

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

ColumnTypeValues
return_idint
item_idint
reasonenumdamaged, wrong_item, size_issue, not_as_described, changed_mind
refund_amountint

Primary key: return_id.

At most one return request per item, always for a delivered item.

Example

The rows must come back in the order shown.

order_items

item_idorder_idseller_idunit_pricedelivered_on
1701109992024-07-02
2702104992024-07-03
37031012992024-07-03
4704107992024-07-05
57052024992024-07-04
67062019992024-07-06
7707203492024-07-06
8708205992024-07-08
970920899NULL
107103015992024-07-09
1 more row

returns

return_iditem_idreasonrefund_amount
11damaged999
23size_issue1299
35wrong_item2499
410changed_mind1599
511damaged1599

Output

seller_iddelivered_itemsreturned_itemsreturn_rate_pct
104250
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.

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

ColumnType
item_idint
order_idint
seller_idint
unit_priceint
delivered_ondate

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

ColumnType
return_idint
item_idint
reasonenum(damaged, wrong_item, size_issue, not_as_described, changed_mind)
refund_amountint

Primary key: return_id.

At most one return request per item, always for a delivered item.

Examples

Example 1

order_items

item_idorder_idseller_idunit_pricedelivered_on
1701109992024-07-02
2702104992024-07-03
37031012992024-07-03
4704107992024-07-05
57052024992024-07-04
67062019992024-07-06
7707203492024-07-06
8708205992024-07-08
970920899NULL
107103015992024-07-09
117113015992024-07-10

returns

return_iditem_idreasonrefund_amount
11damaged999
23size_issue1299
35wrong_item2499
410changed_mind1599
511damaged1599

Output

seller_iddelivered_itemsreturned_itemsreturn_rate_pct
104250

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_id

Another 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 →