The business wants to know which product categories drive the most revenue. Using the products and order_items tables, calculate total revenue per category — return category and revenue, ordered from highest to lowest.
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 |
order_items:
| id | order_id | product_id | quantity | unit_price |
|---|---|---|---|---|
| 1 | 101 | 2 | 2 | 149.99 |
| 2 | 101 | 1 | 3 | 9.99 |
| 3 | 102 | 3 | 1 | 79.99 |
| 4 | 103 | 2 | 1 | 149.99 |
| category | revenue |
|---|---|
| Electronics | 529.96 |
| Tools | 29.97 |
Electronics earns 2×149.99 + 1×79.99 + 1×149.99 = 529.96. Tools earns 3×9.99 = 29.97.