SQL interview question: RANK vs DENSE_RANK
Product Rankings with Tie Handling
The marketplace team needs to rank products by rating, showing both RANK and DENSE_RANK to understand tie handling.
Rank products by rating (highest first) showing both ranking methods.
Return product_name, rating, rank_position, and dense_rank_position. Order by rating descending. Limit to 10.
Note: RANK skips numbers after ties (1,1,3) while DENSE_RANK doesn't (1,1,2).
Demonstrates difference between RANK() and DENSE_RANK()
Tables
products 20 rows
- product_id integer PK
- product_name text
- category text
- price real
- rating real
| product_id | product_name | category | price | rating |
|---|---|---|---|---|
| 1 | MacBook Pro | Electronics | 1999.99 | 4.8 |
| 2 | iPad Air | Electronics | 599.99 | 4.8 |
| 3 | AirPods Pro | Electronics | 249.99 | 4.7 |