The product team wants to identify star performers in each category. Using the products and order_items tables, return category, name, revenue (sum of quantity × unit_price), and rnk for the top 2 products by revenue in each category, ordered by category then rnk.
products
| column | type |
|---|---|
| id | INTEGER |
| name | TEXT |
| category | TEXT |
| price | NUMERIC |
order_items
| column | type |
|---|---|
| id | INTEGER |
| order_id | INTEGER |
| product_id | INTEGER |
| quantity | INTEGER |
| unit_price | NUMERIC |
products
| id | name | category | price |
|---|---|---|---|
| 1 | Laptop | Electronics | 1200.00 |
| 2 | Phone | Electronics | 800.00 |
| 3 | Tablet | Electronics | 400.00 |
| 4 | Jacket | Apparel | 300.00 |
| 5 | Shirt | Apparel | 150.00 |
order_items
| id | order_id | product_id | quantity | unit_price |
|---|---|---|---|---|
| 1 | 1 | 1 | 1 | 1200.00 |
| 2 | 1 | 2 | 1 | 800.00 |
| 3 | 2 | 3 | 1 | 400.00 |
| 4 | 2 | 4 | 1 | 300.00 |
| 5 | 3 | 5 | 1 | 150.00 |
| category | name | revenue | rnk |
|---|---|---|---|
| Apparel | Jacket | 300.00 | 1 |
| Apparel | Shirt | 150.00 | 2 |
| Electronics | Laptop | 1200.00 | 1 |
| Electronics | Phone | 800.00 | 2 |
Tablet (Electronics, 400.00) is ranked 3rd in its category and is excluded. Jacket and Shirt are the only two Apparel items so both appear at ranks 1 and 2.