Write SQL to rank top products per category
Company: Coinbase
Role: Machine Learning Engineer
Category: Coding & Algorithms
Difficulty: medium
Interview Round: Onsite
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`).
Quick Answer: This question evaluates SQL proficiency in aggregation, time-based filtering, joining transactional and reference tables, and ranking/windowing techniques to compute and deterministically order product revenue with tie-breaking by product_id.