Explain the key difference between ROW_NUMBER, RANK, and DENSE_RANK with a practical example.

ROW_NUMBER assigns unique sequential numbers (1,2,3,4,5,6) regardless of ties - perfect for pagination. RANK gives tied values the same rank but skips subsequent ranks (1,2,2,4,4,6) - perfect for competitions. DENSE_RANK gives tied values the same rank but continues sequentially (1,2,2,3,3,4) - perfect for business tiers. Example with salaries $100k, $90k, $90k, $80k: ROW_NUMBER = 1,2,3,4; RANK = 1,2,2,4; DENSE_RANK = 1,2,2,3.

When would you use DENSE_RANK instead of RANK?

Use DENSE_RANK when you need continuous levels without gaps, typically for business categorization. Examples: customer loyalty tiers (Bronze, Silver, Gold), product rating categories, academic grade levels, or performance bands. DENSE_RANK ensures no "missing" levels - if customers tie for Silver, the next tier is Gold, not Platinum. Use RANK for competitions where gaps after ties make sense (Olympic-style scoring).

Write a query to implement pagination showing products 21-40 with consistent ordering.

WITH paginated_products AS (

SELECT product_id, product_name, price,

ROW_NUMBER() OVER(

ORDER BY price DESC, avg_rating DESC, product_id

) as position

FROM products

WHERE category = 'Electronics' AND in_stock = true

)

SELECT product_id, product_name, price

FROM paginated_products

WHERE position BETWEEN 21 AND 40;

ROW_NUMBER ensures unique sequential positions for pagination. Multiple ORDER BY columns provide deterministic tie-breaking. BETWEEN 21 AND 40 gets exactly page 2 (assuming 20 items per page).

Create a sales leaderboard that handles ties fairly and shows top 5 per region.

WITH regional_rankings AS (

SELECT salesperson_name, region, total_sales,

RANK() OVER(

PARTITION BY region

ORDER BY total_sales DESC, deals_closed DESC

) as regional_rank

FROM sales_performance

WHERE quarter = '2024-Q1'

)

SELECT * FROM regional_rankings

WHERE regional_rank <= 5

ORDER BY region, regional_rank;

RANK handles ties fairly - tied salespeople get same rank. PARTITION BY region creates separate competitions per region. WHERE regional_rank <= 5 gets top 5, but may return more if there are ties at rank 5.