Top 3 Products by Revenue per Category (with RANK)
Preview mode. Log in to edit, run, submit, and save progress.
Top 3 Products by Revenue per Category (with RANK)
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 |
Examples
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.