Product Sales Summary per Category per Year
Preview mode. Log in to edit, run, submit, and save progress.
Medium
Product Sales Summary per Category per Year
You are given a table of orders. Each order has a product name, a category, a quantity sold, a unit price, and an order date. Write a SQL query to find for each year and category: the number of orders, the total units sold, and the total revenue (quantity × unit_price). Return the result ordered by year and category. Table: Orders
| Column Name | Type | Description |
|---|---|---|
| id | INT | Primary key |
| product_name | VARCHAR | Name of the product |
| category | VARCHAR | Product category |
| quantity | INT | Units sold in this order |
| unit_price | INT | Price per unit |
| order_date | DATE | Date the order was placed |
Examples
Example 1
Group by year (extracted from order_date) and category. Count orders, sum quantities, and sum quantity*unit_price as revenue.
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.
query.sql