Level 6 · 2025 refunds by category

Many questions need more than two tables. Here the return knows its order line, the order line knows its product, and only the product knows its category. You walk the chain one join at a time.

Chaining joins

Each JOIN adds one table using a key it shares with a table already in the query:

SELECT o.channel,
       COUNT(*) AS returns,
       ROUND(SUM(r.refund_amount), 2) AS refunded
FROM returns AS r
JOIN order_items AS i ON i.order_item_id = r.order_item_id
JOIN orders AS o ON o.order_id = i.order_id
WHERE r.returned_on >= '2025-01-01'
GROUP BY o.channel
ORDER BY returns DESC;

This walks from returns to the order line, then to the order, and reports 2025 returns per channel: web 289, app 286 and marketplace 84. Your ticket takes the other branch, from the order line to products.

Before grouping, ask what one row means after the joins. A return matches one order line and a line has one product, so each return is still one row. That is why COUNT(*) counts returns here. Joining in the other direction, from a product to its many lines, would multiply rows instead.

Notice also that the date filter is on returned_on, the date the money went back, not the order date. A return processed in January 2025 for a December 2024 order is a 2025 refund.

A quick sanity check after any chain of joins: count the rows before grouping and compare with the table whose grain you want. Here the joined rows for 2025 number 659, the same as the 2025 rows in returns, so no row was lost or doubled along the way.

Common mistakes

• Skipping the bridge. There is no column linking returns to products directly, so the path has to go through order_items.
• Wrong date column. Filtering on the order date answers a different question.
• Sorting by the wrong number. Decor has the most 2025 returns (179) but an average refund of only $100.82, while Furniture has 35 returns averaging $994.22. Sort by total_refund, not by returns.

In SQL Server

The joins are identical. Only the date literal style differs if you prefer typed values: WHERE r.returned_on >= '20250101' AND r.returned_on < '20260101' works in SQL Server, and '2025-01-01' works too for a date column.