MediumE-commerce14 min

Categories with a high average line total

JOINs GROUP BY HAVING

Loading the editor…

MediumE-commerce14 min

Categories with a high average line total

JOINs GROUP BY HAVING

Challenge

Find product categories whose average order-line total is at least 10000 cents.

Business context

Merchandising is separating premium categories from the rest of the catalog. The average is taken over order lines, including cancelled and pending lines, because this cut is about price mix rather than recognized revenue.

Instructions

Return category and avg_line_cents. The line total is quantity times unit_price_cents. Keep categories whose average line total is at least 10000. Sort by avg_line_cents descending.

Schema

customers

id INTEGER · primary key
name TEXT
email TEXT
city TEXT
country TEXT
created_at TEXT · YYYY-MM-DD

products

id INTEGER · primary key
name TEXT
category TEXT
price_cents INTEGER
stock INTEGER · nullable

orders

id INTEGER · primary key
customer_id INTEGER · customers.id
status TEXT · paid, shipped, pending, cancelled
ordered_at TEXT
shipped_at TEXT · nullable

order_items

id INTEGER · primary key
order_id INTEGER
product_id INTEGER
quantity INTEGER
unit_price_cents INTEGER

payments

id INTEGER · primary key
order_id INTEGER
method TEXT
amount_cents INTEGER
paid_at TEXT · nullable
status TEXT · succeeded, failed, refunded

Sample rows

customers
id	name	email	city	country	created_at
1	Alice Chen	alice@northwind.io	Berlin	Germany	2023-01-12
2	Ben Okonkwo	ben@okonkwo.co	Lagos	Nigeria	2023-03-02
3	Clara Singh	clara@singh.dev	London	United Kingdom	2022-11-20

products
id	name	category	price_cents	stock
1	Trail Bottle	Outdoors	2400	120
2	Desk Lamp	Home	4250	40
3	Noise-canceling Headphones	Electronics	18900	15

orders
id	customer_id	status	ordered_at	shipped_at
1	1	paid	2024-11-02	2024-11-04
2	1	paid	2024-12-14	2024-12-16
3	3	shipped	2025-01-09	2025-01-11

order_items
id	order_id	product_id	quantity	unit_price_cents
1	1	1	2	2400
2	1	4	1	1200
3	2	3	1	18900

payments
id	order_id	method	amount_cents	paid_at	status
1	1	card	6000	2024-11-02	succeeded
2	2	card	18900	2024-12-14	succeeded
3	3	paypal	22000	2025-01-09	succeeded

Draft saves automatically

Ctrl or Cmd + Enter runs the query. The database resets on every run.

Run the query to see rows from the sample database.

Next exercise: Departments with more than two people