Top Two Products per Category
Using products and order_items, find the two best-selling products in each category by total units sold (sum of quantity). If two products tie on units, the one whose name comes first alphabetically wins. Products with no sales are not considered. A category with only one selling product returns one row. Product names are unique.
Return columns category, name, units_sold. Order by category ascending, units_sold descending, then name ascending.
Schema
CREATE TABLE products ( product_id INTEGER PRIMARY KEY, name TEXT NOT NULL, category TEXT NOT NULL ); CREATE TABLE order_items ( order_id INTEGER NOT NULL, product_id INTEGER NOT NULL, quantity INTEGER NOT NULL );
Sample data
products
| product_id | name | category |
|---|---|---|
| 1 | Desk Lamp | Home |
| 2 | Throw Pillow | Home |
| 3 | Wall Clock | Home |
| 4 | USB-C Cable | Electronics |
| 5 | Earbuds | Electronics |
| 6 | Power Bank | Electronics |
order_items
| order_id | product_id | quantity |
|---|---|---|
| 1 | 1 | 2 |
| 1 | 4 | 3 |
| 2 | 2 | 5 |
| 2 | 5 | 1 |
| 3 | 3 | 1 |
| 3 | 4 | 2 |
| 4 | 6 | 4 |
| 4 | 1 | 1 |
| 5 | 5 | 2 |
Hint 1
First total the units per product, then rank the products inside each category.
Hint 2
ROW_NUMBER, unlike RANK, never gives two rows the same number, so "top 2" means exactly two rows.
Good practice for: Data Analyst, Business Analyst, Product Manager, Data Engineer
Run checks your query on the sample data (Ctrl + '). Submit also checks hidden databases.