Rank Scores SQL Query | SQL Interview Question

Problem statement

Prompt

Given the scores table below, write a query that returns each score's rank, where:

identical scores share the same rank;
the next rank is the next distinct score position — ranks must have no gaps after a tie.

So scores 100, 100, 90 rank as 1, 1, 2 (never 1, 1, 3).

The result must contain: id, score, and rank, ordered by rank ascending, then id ascending.

Example

id	score
1	100
2	100
3	90

Expected result:

id	score	rank
1	100	1
2	100	1
3	90	2

Constraints

scores.id is the primary key.
scores.score may be NULL; NULL scores should be excluded from the result.

Tips

Ties with no gaps is the textbook definition of DENSE_RANK() OVER (ORDER BY score DESC).
RANK() would skip to 3 after a tie; ROW_NUMBER() would number tied rows 1, 2. Know all three.

Schema

CREATE TABLE scores (
    id    INTEGER PRIMARY KEY,
    score INTEGER  -- NULL allowed
);

Sample data

INSERT INTO scores (id, score) VALUES
  (1, 100),
  (2, 100),
  (3, 90),
  (4, 85),
  (5, 85);

Additional hidden fixtures are applied during validation to test edge cases.

Solution

One correct approach — try solving it yourself in the practice editor first, then compare. There is usually more than one valid solution.

-- Reference: dense ranking, ties share a rank, no gaps.
SELECT id, score,
       DENSE_RANK() OVER (ORDER BY score DESC) AS rank
FROM scores
WHERE score IS NOT NULL
ORDER BY rank, id;

Key concepts

DENSE_RANK
RANK vs DENSE_RANK
ORDER BY

Rank Scores