Level 6 · Boss: where do returns come from?

Map the path first

returns only knows the order_item_id that came back. Everything else is a join away:

returns --order_item_id--> order_items --product_id--> products   (category)
                                       --order_id----> orders     (channel)

Sketching this path before you write code is a good habit for any multi-table question. Note the grain of each hop: one return per line at most (one-to-one), many lines per product and per order (many-to-one). Those are exactly the validate= values to use.

Step 1: counts by category

returned = (
    returns
    .merge(order_items[["order_item_id", "product_id"]], on="order_item_id", validate="one_to_one")
    .merge(products[["product_id", "category"]], on="product_id", validate="many_to_one")
)
returned.groupby("category", as_index=False).agg(n_returns=("return_id", "count"))

len(returned) should equal len(returns). If it doesn’t, a join lost or duplicated returns.

Step 2: rates, not counts

Raw counts favour whatever sells most. A fair comparison divides by how much each category sold. That needs a denominator from all order lines, not just returned ones:

lines = order_items.merge(products[["product_id", "category"]], on="product_id")
lines = lines.assign(is_returned=lines["order_item_id"].isin(returns["order_item_id"]))

The flag is_returned keeps the table at the line grain, with no merge that could fan out. Then one groupby gives both counts, since the sum of a boolean column counts its True values:

(
    lines.groupby("category", as_index=False)
    .agg(n_lines=("order_item_id", "count"), n_returns=("is_returned", "sum"))
    .assign(return_rate=lambda d: (d["n_returns"] / d["n_lines"]).round(3))
)

assign with a lambda lets you compute a new column from the aggregated table in the same chain. The ranking by rate can differ from the ranking by count, and that difference is the insight: a category might generate many returns simply because it sells a lot, while another is returned unusually often for its volume.

Returns here include every line of refunded orders, so the rate mixes two causes: whole-order refunds and individual returned items. A careful analysis would split them; mention it if you present this.

Step 3: money by channel

refunds = (
    returns
    .merge(order_items[["order_item_id", "order_id"]], on="order_item_id")
    .merge(orders[["order_id", "channel"]], on="order_id")
)
refunds.groupby("channel", as_index=False).agg(total_refund=("refund_amount", "sum"))

refund_amount is a per-return value, so summing it on a table with one row per return is safe. Compare the total with returns["refund_amount"].sum() to confirm.

Common mistakes

• Dividing returns by the number of products or orders instead of order lines.
• Getting return rates over 1, a sure sign of a fan-out in the numerator.
• Using count on is_returned, which counts all lines, instead of sum.