What are common mistakes on SQL: Score Ranking with Ties (RANK vs DENSE_RANK)?

Filtering on an aggregate in WHERE rather than HAVING. An inner join where a left join is meant, which silently drops the rows the question was about - the customers with zero orders. COUNT(*) versus COUNT(column). The second skips nulls, and that difference is often the entire question.

gitGood.dev

← Back to problems

SQL: Score Ranking with Ties (RANK vs DENSE_RANK)

MediumDatabases

Description

This problem is paper-only - see explanation for the canonical SQL solution.

Given Scores(id, score), return each score with its DENSE_RANK (no gaps after ties), sorted by rank ascending then id ascending.

For the auto-grader: return [{ id, score, rank }].

Example 1

Input: scores = [{id:1,score:90},{id:2,score:85},{id:3,score:90},{id:4,score:70}]

Output: [{id:1,score:90,rank:1},{id:3,score:90,rank:1},{id:2,score:85,rank:2},{id:4,score:70,rank:3}]

Explanation: Two 90s tie at rank 1. Next score 85 is rank 2 (no gap, that's DENSE_RANK).

Constraints

- 0 <= scores.length <= 10^4 - score is a non-negative integer.

SQL: Score Ranking with Ties (RANK vs DENSE_RANK): approach, complexity and pitfalls

A medium sql problem, graded against 6 test cases (4 of them hidden).

The pattern: SQL

Query-writing problems - joins, aggregation, window functions, and query tuning.

Reach for it when you see: A schema and a question about the data rather than a function signature.

If you are stuck

Show the 2-step hint ladder

DENSE_RANK() OVER (ORDER BY score DESC).
RANK() leaves gaps after ties (1, 1, 3); DENSE_RANK() does not (1, 1, 2).

The optimal approach

Show the approach: Canonical SQL:

```sql

SELECT id, score,

DENSE_RANK() OVER (ORDER BY score DESC) AS rank

FROM Scores

ORDER BY rank, id;

```

Three ranking functions side by side, scores [90, 90, 85, 70]:

| function | output |

|---|---|

| ROW_NUMBER() | 1, 2, 3, 4 |

| RANK() | 1, 1, 3, 4 |

| DENSE_RANK() | 1, 1, 2, 3 |

Pick based on what "rank" means in the spec: leaderboard typically wants `DENSE_RANK`, sports placings typically want `RANK`, anything that needs unique ordering wants `ROW_NUMBER`.

The full reference solution in every supported language stays in the editor above - reveal it there once you have had a real attempt.

What the tests check

Read off this problem's own test suite, so these are the cases a submission actually has to survive.

Empty input. At least one case passes an empty collection. Make sure your loop bounds and any "first element" read survive a length of zero.
Single element. A one-element case is included. Two-pointer and window solutions frequently return the wrong thing when the two indices start out equal.

Common SQL mistakes

These apply to the pattern as a whole, not just this problem.

Filtering on an aggregate in `WHERE` rather than `HAVING`.
An inner join where a left join is meant, which silently drops the rows the question was about - the customers with zero orders.
`COUNT(*)` versus `COUNT(column)`. The second skips nulls, and that difference is often the entire question.