Product Sales Summary per Category per Year

Preview mode. Log in to edit, run, submit, and save progress.

Medium

Description

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

Database Schema (Inferred)

Orders

Column Name	Example Value
id	1
product_name	Laptop
category	Electronics
quantity	2
unit_price	800
order_date	2022-03-15

Example

Orders

id	product_name	category	quantity	unit_price	order_date
1	Laptop	Electronics	2	800	2022-03-15
2	Phone	Electronics	5	400	2022-07-20
3	Desk	Furniture	3	200	2022-03-10
4	Chair	Furniture	4	150	2023-01-05
5	Laptop	Electronics	1	800	2023-05-18
6	Lamp	Furniture	2	50	2023-08-22

Output

year	category	order_count	total_units_sold	total_revenue
2022	Electronics	2	7	3600
2022	Furniture	1	3	600
2023	Electronics	1	1	800
2023	Furniture	2	6	700

Explanation:

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.