Rank Scores

Rank Scores

What is this problem about?

The Rank Scores SQL problem asks you to rank scores without gaps: the highest score gets rank 1, and each unique score gets the next consecutive rank (no gaps). This is the DENSE_RANK() function applied to scores in descending order. The database interview pattern demonstrates dense ranking.

Why is this asked in interviews?

Microsoft, Meta, Amazon, Google, and Bloomberg ask this as the standard SQL ranking problem. It tests knowledge of DENSE_RANK() vs RANK() vs ROW_NUMBER() — an essential SQL distinction.

Algorithmic pattern used

DENSE_RANK window function.

SELECT score, DENSE_RANK() OVER (ORDER BY score DESC) AS rank
FROM Scores
ORDER BY score DESC

Example explanation

Scores: [3.50, 3.65, 4.00, 3.85, 4.00, 3.65]. DENSE_RANK: 4.00→1, 4.00→1, 3.85→2, 3.65→3, 3.65→3, 3.50→4. Note: no gap after ties (rank 3 after two 3.65s, not rank 4).

Common mistakes candidates make

• Using RANK() (creates gaps: 1,1,3,4 instead of 1,1,2,3).
• Using ROW_NUMBER() (gives unique ranks, ignoring ties).
• Not ordering by score DESC.
• Confusing that DENSE_RANK gives no gaps in rank values.

Interview preparation tip

Know all three ranking functions: RANK() creates gaps after ties; DENSE_RANK() gives consecutive ranks with no gaps; ROW_NUMBER() gives unique sequential numbers ignoring ties. Rank Scores requires DENSE_RANK(). Practice all three on the same dataset to understand the differences. This distinction appears in every SQL interview that involves ranking or ordering records.