Top 3 Products by Revenue per Category (with RANK)

Preview mode. Log in to edit, run, submit, and save progress.

Medium

Description

You are given a table of product sales. Each row contains a product name, its category, and its total revenue. Write a SQL query to return the top 3 revenue-ranked products per category using RANK() (not ROW_NUMBER or DENSE_RANK). If products tie, they share the same rank and both are included. A tie at rank 1 between two products means rank 3 is the next product (rank 2 is skipped). Return category, product, revenue, and rnk for all rows where rnk <= 3, ordered by category then rnk then product. Table: ProductSales

Column Name	Type	Description
id	INT	Primary key
product	VARCHAR	Product name
category	VARCHAR	Product category
revenue	INT	Total revenue

Database Schema (Inferred)

ProductSales

Column Name	Example Value
id	1
product	P1
category	Electronics
revenue	5000

Example

ProductSales

id	product	category	revenue
1	P1	Electronics	5000
2	P2	Electronics	8000
3	P3	Electronics	8000
4	P4	Electronics	3000
5	P5	Electronics	7000
6	P6	Clothing	2000
7	P7	Clothing	4000
8	P8	Clothing	4000
9	P9	Clothing	1000
10	P10	Food	500
11	P11	Food	900

Output

category	product	revenue	rnk
Clothing	P7	4000	1
Clothing	P8	4000	1
Clothing	P6	2000	3
Electronics	P2	8000	1
Electronics	P3	8000	1
Electronics	P5	7000	3
Food	P11	900	1
Food	P10	500	2

Explanation:

RANK() assigns the same rank to ties and skips subsequent ranks. P2 and P3 both get rank 1, so P5 gets rank 3 (rank 2 is skipped). Only rnk <= 3 are included.

Approach hint

Start with a simple approach, explain the trade-off, then move toward a cleaner or more scalable solution.

Common mistake

Skipping assumptions, edge cases, or trade-offs can make an otherwise good answer feel incomplete.