What are returns costing us?

HardAmazonleft joinreturn ratemodeproblem productspercentiles· 160 XP (40 per step)

Mock interview. You're interviewing for a senior analyst job on an online shop's customer experience team. The interviewer says: "Returns are expensive: shipping, handling, and often the item can't be resold. Help us see where they come from."

An item's value is quantity * unit_price * (1 - discount), where an empty discount means no discount.
Each row in returns is one product returned from one order. Match it to order_items on both order_id and product_id.
Leave out cancelled orders.

Step 1: Returns by category

"For each product category, show the value sold, the value returned, and the return rate (returned value / value sold)."

Expected output

Return: category, value sold, value returned and return rate (name the columns however you like)

Columns category, value_sold, value_returned, return_rate (a percentage rounded to 1 decimal); money rounded to 2 decimals; sorted by return_rate, highest first

Example of the shape (made-up values):

category	value_sold	value_returned	return_rate
Garden	6000.00	360.00	6.0

Step 1 of 4

Write your query here. Ctrl + Enter submits.