Level 2 · Measure the return rate in SQL

What you are checking, and why

Before you compute a rate, look at what the returns belong to. Every return row points to one order line, and every line belongs to an order with a status. If returns behave very differently by status, one rate for everything would hide that.

To count returned and not-returned lines together, keep every line with a LEFT JOIN:

FROM order_items AS i
JOIN orders AS o ON o.order_id = i.order_id
LEFT JOIN returns AS r ON r.order_item_id = i.order_item_id

Lines without a return get NULL in every r column. COUNT(*) counts all lines, COUNT(r.return_id) counts only the returned ones, and their ratio is the return rate. The result of step 1 shows refunded orders at 96.6% and completed orders at 3.3%: two different processes, which is why the rest of the case uses completed orders only.

The rate and the money

Step 2 keeps the same join, adds products for the category and filters to completed orders and real quantities. Two measures come out of one query:

• the return rate, COUNT(r.return_id) * 100.0 / COUNT(*), which tells Support where the work is;
• the refund share, SUM(r.refund_amount) * 100.0 / SUM(net revenue), which tells Finance where the money goes.

SUM(r.refund_amount) adds only the returned lines, because the others are NULL and SUM skips them. Both percentages are computed from unrounded totals and rounded last.

Reasons as columns

Step 3 starts from returns, since only returned lines have a reason, and turns each reason into its own column with conditional aggregation:

SUM(CASE WHEN r.reason = 'damaged' THEN 1 ELSE 0 END) * 100.0 / COUNT(*) AS damaged_pct

COUNT(*) here is the category’s own returns, so each row adds up to 100%.

Reading the result

category	return_rate_pct	refund_share_pct	top reason
Textiles	4.7	5.1	changed_mind 57.9%
Furniture	4.4	4.7	damaged 46.0%
Lighting	3.8	4.3	damaged 38.3%
Kitchen	2.0	2.5	changed_mind 40.6%

Two different problems show up. Textiles come back because customers change their minds: color, feel or size not as expected. Furniture and Lighting come back broken. Those need different fixes.

Pitfalls

• An inner join to returns keeps only returned lines and every rate becomes 100%.
• COUNT(*) / COUNT(*) between integers can truncate in some databases; * 100.0 keeps it decimal.
• Some changed-mind refunds on Furniture and Outdoor are 15% below the line value: a restocking fee. Compare refunds with sales, not with returned line values, when you talk about money lost.