Returned Value Share by Category
For each category, compare the value of completed orders with the value of returned orders, and compute what share of the combined value was returned.
Expected output
category, completed_value, returned_value (both rounded to 2 decimals), returned_pct = returned / (completed + returned) as a percentage rounded to 1 decimal. Sorted by category.
Row order is checked. Column names are not checked; numbers are compared to 2 decimals.
Dataset: E-commerce store
An online store selling electronics, home goods, books, apparel and beauty products to customers in six countries (Jan 2025 to Mar 2026).
customers
One row per registered customer.
| customer_id | INTEGER | primary key |
| name | TEXT | |
| country | TEXT | US, UK, DE, IN, BR or CA |
| signup_date | TEXT | YYYY-MM-DD |
| referred_by | INTEGER | customer_id of the referrer, NULL if none |
products
Product catalog with current list price.
| product_id | INTEGER | primary key |
| name | TEXT | |
| category | TEXT | |
| price | REAL | list price in USD |
orders
One row per order. Only completed orders count as revenue.
| order_id | INTEGER | primary key, increases with order_date |
| customer_id | INTEGER | |
| order_date | TEXT | YYYY-MM-DD |
| status | TEXT | completed, cancelled or returned |
order_items
Line items. Revenue of a line = quantity * unit_price (unit_price may include a discount).
| order_id | INTEGER | |
| product_id | INTEGER | |
| quantity | INTEGER | |
| unit_price | REAL | price actually charged per unit |
Engine: SQLite. Dates are text (YYYY-MM-DD); use strftime(), date() and julianday() for date math.
Related problems
- Revenue by Product Category (easy)
- Customers Who Never Ordered (easy)
- Quarterly Cancellation Rate (medium)
- Cancellation Rate by City (easy)
- Fare per Kilometer by Vehicle Type (easy)