Write a query using a CTE to identify the top three products by revenue per category.
Use the provided product and order item data. Revenue is the sum of quantity multiplied by unit price. Break revenue ties by the smaller product ID.
category, product_id, product_name, revenue, and product_rank.| Column | Type | Description |
|---|---|---|
| product_idPK | INT | Unique product identifier |
| category | VARCHAR(100) | Product category |
| product_name | VARCHAR(150) | Product display name |
| Column | Type | Description |
|---|---|---|
| order_item_idPK | INT | Unique order item identifier |
| product_id | INT | Product purchased in the order item |
| quantity | INT | Number of units purchased |
| unit_price | NUMERIC(10,2) | Price per unit at purchase time |