Marketing wants to highlight the best-selling product in each category. Using the products and order_items tables, find the top product by revenue in each category — return category, name, and revenue, ordered by category.
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 | Widget | Tools | 9.99 |
| 2 | Gadget | Electronics | 149.99 |
| 3 | Gizmo | Electronics | 79.99 |
| 4 | Hammer | Tools | 29.99 |
order_items
| id | order_id | product_id | quantity | unit_price |
|---|---|---|---|---|
| 1 | 101 | 1 | 10 | 9.99 |
| 2 | 102 | 2 | 3 | 149.99 |
| 3 | 103 | 3 | 5 | 79.99 |
| 4 | 104 | 4 | 2 | 29.99 |
| category | name | revenue |
|---|---|---|
| Electronics | Gadget | 449.97 |
| Tools | Widget | 99.90 |
Gadget (3 × 149.99 = 449.97) beats Gizmo (5 × 79.99 = 399.95) in Electronics. Widget (10 × 9.99 = 99.90) beats Hammer (2 × 29.99 = 59.98) in Tools.