Top 3 Products per Category

SQL coding challenge · Difficulty: medium · +100 XP

Table: products

+------------+---------+

| Column     | Type    |

+------------+---------+

| product_id | INT     |
| name       | VARCHAR |
| category   | VARCHAR |
| revenue    | DECIMAL |

+------------+---------+

Problem

-------

For each category, find the top 3 products

by revenue. If two products tie, rank the

one with the lower product_id higher.

Return: category, product_name, revenue,

rank_in_category

Order: category ASC, rank ASC

Example Input

-------------

+----+----------+----------+---------+

| id | name     | category | revenue |

+----+----------+----------+---------+

|  1 | Widget A | Gadgets  | 5000.00 |
|  2 | Widget B | Gadgets  | 8000.00 |
|  3 | Widget C | Gadgets  | 6500.00 |
|  4 | Gizmo X  | Gadgets  | 7200.00 |
|  5 | Pen Pro  | Office   | 1200.00 |
|  6 | Desk Pro | Office   | 3500.00 |
|  7 | Chair X  | Office   | 2800.00 |

+----+----------+----------+---------+

Expected Output

+----------+----------+---------+------+

| category | name     | revenue | rank |

+----------+----------+---------+------+

| Gadgets  | Widget B | 8000.00 |  1   |
| Gadgets  | Gizmo X  | 7200.00 |  2   |
| Gadgets  | Widget C | 6500.00 |  3   |
| Office   | Desk Pro | 3500.00 |  1   |
| Office   | Chair X  | 2800.00 |  2   |
| Office   | Pen Pro  | 1200.00 |  3   |

+----------+----------+---------+------+

Hint

----

Use DENSE_RANK() OVER (PARTITION BY

category ORDER BY revenue DESC)