Level 6 · Boss: category scorecard

This boss chains several joins. The trick is to keep track of the grain at every step and to know which joins can change the row count.

Step 1: three tables, one grain

Revenue lives on order lines, so order_items is the base. Each line has exactly one order and one product, so joining orders and products adds columns without adding rows. That is a safe join: many-to-one. Filter on o.status and i.quantity, then group by category.

Step 2: return rate with a LEFT JOIN

Returns are optional: most lines are never returned. A LEFT JOIN from lines to returns keeps every sold line and fills return columns with NULL when there is none:

LEFT JOIN returns AS r ON r.order_item_id = i.order_item_id

Then COUNT(*) counts all lines and COUNT(r.return_id) counts only those with a return. Because each line has at most one return in this dataset, this join does not fan out. In a real system where a line could be returned in two parcels, you would count DISTINCT i.order_item_id among returned lines, or pre-aggregate returns per line first. Always check: SELECT order_item_id, COUNT(*) FROM returns GROUP BY 1 HAVING COUNT(*) > 1 tells you in a second.

The filters on o.status and i.quantity are on the base tables, not on returns, so keeping them in WHERE is correct. A filter such as r.reason = 'damaged' in WHERE would turn the LEFT JOIN into an inner one, as you saw two levels ago. It would belong in ON.

A return rate is a rate of lines here. Megan might also want a rate of units or of revenue. Say which one you computed in your reply.

Step 3: top customers with names

Group by customer_id and also by the name columns you select. Grouping by name alone would merge two different customers who share a name, and Harbor & Pine has some duplicate customer records, which you will untangle in chapter 7. Always group by the key, and add descriptive columns alongside it.

Final check

Add up step 1’s revenue across categories, and compare it with step 3’s style of query without the LIMIT and summed over all customers. Both should match exactly: they are the same lines grouped two ways. If they do not, a join dropped or duplicated something. That reconciliation habit is what makes people trust your numbers.