Write SQL to rank top products per category

You are given two tables:

products

• product_id (int, primary key)
• category (string)

order_items

• order_id (int)
• product_id (int)
• quantity (int)
• unit_price (decimal)
• created_at (timestamp)

Task

1. Compute each product’s total revenue in calendar year 2024, where revenue for an item is quantity * unit_price.
2. For each category, return the top 3 products by total revenue in 2024.
3. If multiple products tie, break ties by smaller product_id first.

Output Return rows with:

• category
• product_id
• total_revenue
• rank_in_category (1 = highest revenue within the category)

Sort the final output by category, then rank_in_category, then product_id.

Notes/Constraints

• A product with no sales in 2024 should not appear.
• Use a solution that involves an aggregation step and a window function (e.g., ROW_NUMBER, RANK, or DENSE_RANK).