Product Sales Summary by Category
Preview mode. Log in to edit, run, submit, and save progress.
Product Sales Summary by Category
You are given two tables: Products and Orders. Each product belongs to a category and has a price. Each order references a product and includes a quantity. Write a SQL query to find for each product category: the total number of orders, the total units sold, and the total revenue (quantity × price). Categories with no orders should still appear with 0 order_count, NULL total_units, and NULL total_revenue. Return the result ordered by total_revenue descending (NULLs last). Table: Products
| Column Name | Type | Description |
|---|---|---|
| id | INT | Primary key |
| name | VARCHAR | Product name |
| category | VARCHAR | Product category |
| price | DECIMAL | Unit price |
Table: Orders
| Column Name | Type | Description |
|---|---|---|
| id | INT | Primary key |
| product_id | INT | References Products.id |
| quantity | INT | Units ordered |
| order_date | DATE | Date of the order |
Examples
Group orders by category via a join on Products. SUM(quantity * price) gives revenue. ORDER BY total_revenue DESC.
Approach hint
Start with a simple approach, explain the trade-off, then move toward a cleaner or more scalable solution.
Common mistake
Skipping assumptions, edge cases, or trade-offs can make an otherwise good answer feel incomplete.