Product Sales Summary by Category

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

Medium

Description

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

Database Schema (Inferred)

Products

Column Name	Example Value
id	1
name	Laptop
category	Electronics
price	1200

Orders

Column Name	Example Value
id	1
product_id	1
quantity	2
order_date	2023-01-10

Example

Products

id	name	category	price
1	Laptop	Electronics	1200
2	Phone	Electronics	800
3	Desk	Furniture	400
4	Chair	Furniture	250
5	Notebook	Stationery	5

Orders

id	product_id	quantity	order_date
1	1	2	2023-01-10
2	2	5	2023-01-15
3	3	1	2023-01-20
4	4	3	2023-02-05
5	5	100	2023-02-10
6	1	1	2023-02-15

Output

category	order_count	total_units	total_revenue
Electronics	3	8	7600
Furniture	2	4	1150
Stationery	1	100	500

Explanation:

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.