Bestsellers within each category

For non-cancelled orders, total the units (quantity) sold per catalog item. Return category, name, units and category_rank, where rank 1 is the item with the most units in its category.

what to return

Columns: categorynameunitscategory_rank in this order
Rows: 12 rows · any order
Watch for: Tied items share a rank and the next rank is not skipped (1, 1, 2).
: Items with no units sold are not listed.
Skill: Rank aggregated values per group with DENSE_RANK. Refresher: Latest row per group →

expected result

Your query should return exactly these columns and values. Run it and compare.

expected result12 rows · any order

category	name	units	category_rank
Garden	Seed Starter Kit	6	1
Garden	Pruning Shears	5	2
Garden	Garden Hose	4	3

first 3 of 12 rows

data you can query

Sample rows from each table. Each column shows its type underneath; → marks a link to another table.

order_lines4 columns · 48 rows

order_idinteger → sales_orders	item_idinteger → catalog_items	quantityinteger	unit_pricereal
101	1	1	39
101	3	2	19.5
102	4	1	59
103	5	1	45
103	10	3	12

showing 5 of 48 rows

sales_orders5 columns · 33 rows

idinteger · key	shopper_idinteger → shoppers	order_datetext	statustext	delivered_datetext
101	1	2025-01-04	delivered	2025-01-08
102	2	2025-01-06	delivered	2025-01-07
103	1	2025-01-15	delivered	2025-01-20
104	3	2025-01-18	cancelled	NULL
105	4	2025-01-22	delivered	2025-01-31

showing 5 of 33 rows

catalog_items4 columns · 13 rows

idinteger · key	nametext	categorytext	pricereal
1	Desk Lamp	Lighting	39
2	Floor lamp	Lighting	89
3	String Lights	Lighting	19.5
4	Chef Knife	Kitchen	59
5	Cast Iron Pan	Kitchen	45

showing 5 of 13 rows

solution.sql

Hints and the answer are always available. Using them counts the question as solved with help.