The Return Problem: Return Rate by Product Category & Reason Code Using SQL Aggregations

Business Context

RefundRadar is a mid-size e-commerce retailer operating across five product categories — Electronics, Apparel, Home & Garden, Beauty & Personal Care, and Sports & Outdoors — processing approximately 95,000 orders per month with $5.6M in monthly GMV. While the platform has maintained steady revenue growth over the past year, the operations team has noticed that return processing costs have quietly risen to nearly 11% of total revenue. The Head of Operations suspects that certain product categories and specific return reasons are driving a disproportionate share of returns, but without a breakdown, the merchandising and logistics teams cannot take targeted corrective action. The analysis period is H1 2024 (January 1 – June 30, 2024).

The SQL Challenge

The returns database captures every return transaction as a row in the order_returns table, recording the product category, return reason code, and return status. The corresponding orders table contains the total volume of orders placed. Your task is to write a SQL aggregation query that calculates the return rate — the percentage of orders that were returned — broken down by product category and return reason code. This requires a JOIN between the orders and returns tables, a multi-column GROUP BY, COUNT aggregations, and a derived percentage column using safe division. Results should be sorted by return rate descending to surface the highest-impact combinations first.

Your Role and Deliverable

You are the data analyst on RefundRadar's Operations Analytics team. The Head of Operations has asked you to produce a query that outputs one row per category–reason combination, showing the total orders in that category, the number of returns for that reason, and the return rate as a percentage. This report will feed directly into a weekly operations review and guide the merchandising team's decisions on product description improvements, size guide updates, and quality checks with suppliers.