Code School of Analytics
ExpertWindow Functions50 points

Best-selling product in each category

The buying team wants to know the best seller of each category, measured by total units sold in orders that were not cancelled.

Task: return the category, the product name and its total units sold, one row per category. If two products tie, pick the one whose name comes first alphabetically. Sort by category.

The data you can query

Each table is shown with its column types and the first rows, so you can see exactly what the data looks like.

customers14 rows
id INTEGERname TEXTcity TEXTsignup_date TEXT
1Ayesha KhanLahore2025-11-03
2Bilal AhmedKarachi2025-11-12
3Hina MalikIslamabad2025-12-01
4Usman TariqRawalpindi2025-12-15
5Sana IqbalLahore2026-01-04
6Omar FarooqFaisalabad2026-01-20

Showing the first 6 of 14 rows.

order_items80 rows
id INTEGERorder_id INTEGERproduct_id INTEGERquantity INTEGER
1112
2124
3261
4224
52104
6391

Showing the first 6 of 80 rows.

orders40 rows
id INTEGERcustomer_id INTEGERorder_date TEXTstatus TEXT
132026-01-16Delivered
2112026-06-14Delivered
372026-02-15Delivered
4102026-06-19Delivered
552026-01-06Delivered
692026-03-06Delivered

Showing the first 6 of 40 rows.

products10 rows
id INTEGERname TEXTcategory TEXTprice REAL
1Wireless MouseElectronics1850
2USB CableElectronics450
3Bluetooth SpeakerElectronics4200
4Notebook A4Stationery220
5Gel Pen PackStationery180
6Desk OrganizerStationery950

Showing the first 6 of 10 rows.

Your query runs on SQLite. Write a single SELECT; use LIMIT instead of TOP.

Need a hint?

Sum units per product first, then number the products inside each category with ROW_NUMBER() OVER (PARTITION BY category ORDER BY units DESC, name).