HardE-commerce18 min

Rank products by revenue

Window Functions GROUP BY JOINs

Loading the editor…

HardE-commerce18 min

Rank products by revenue

Window Functions GROUP BY JOINs

Challenge

Rank products by recognized revenue.

Business context

Merchandising wants a rank, not only a sort, so ties can share a place in a later review. Cancelled and pending orders do not count.

Instructions

Return product_name, revenue_cents, and revenue_rank. Revenue is the sum of quantity times unit_price_cents on paid or shipped orders. Use RANK. Sort by rank, then product name. Omit products with no recognized revenue.

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: Customer spend with a CTE