Revenue by Product Category
Finance wants a quick view of which product categories bring in the most money. Calculate total revenue per category, counting only completed orders. Line revenue is quantity times unit_price.
Expected output
category, revenue (rounded to 2 decimals). Highest revenue first.
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
- Customers Who Never Ordered (easy)
- Monthly Revenue Trend (easy)
- Average Order Value by Country (easy)
- Repeat Purchase Rate (medium)
- Referral Leaderboard (medium)