LeetCode 178: Rank Scores Solution in SQL Explained

Ranking scores in a table might feel like assigning leaderboard positions in a competition, and LeetCode 178: Rank Scores is a medium-level challenge that makes it engaging! Given a Scores table with columns id and score, you need to write an SQL query to rank the scores in descending order using dense ranking (consecutive ranks, ties get the same rank), returning a result with score and rank columns. In this blog, we’ll solve it with SQL, exploring two solutions—Dense Rank Window Function (our best solution) and Self Join with Count (a practical alternative). With step-by-step examples, detailed query breakdowns, and tips, you’ll master this problem. While this is an SQL challenge, you can explore Python basics at Python Basics for related coding skills. Let’s rank those scores!

Problem Statement

In LeetCode 178, you’re given a Scores table:

id (int, primary key)
score (decimal)

Your task is to write an SQL query to:

Rank the scores in descending order using dense ranking (ties get the same rank, next distinct score gets the next consecutive rank).
Return a result table with columns score and rank, ordered by score descending.

This builds on ranking problems like LeetCode 177: Nth Highest Salary, extending to all rows, and differs from table joining like LeetCode 175: Combine Two Tables.

Constraints

Table has at least 1 row.
score values are non-negative decimals.
Duplicate scores may exist.

Example

Let’s see a case:

Scores table:
+----+-------+
| id | score |
+----+-------+
| 1  | 3.50  |
| 2  | 3.65  |
| 3  | 4.00  |
| 4  | 3.85  |
| 5  | 4.00  |
| 6  | 3.65  |
+----+-------+
Output:
+-------+------+
| score | rank |
+-------+------+
| 4.00  | 1    |
| 4.00  | 1    |
| 3.85  | 2    |
| 3.65  | 3    |
| 3.65  | 3    |
| 3.50  | 4    |
+-------+------+
Explanation:
<ul>
<li>4.00: rank 1 (highest).</li>
<li>3.85: rank 2 (next distinct).</li>
<li>3.65: rank 3 (ties with 3.65).</li>
<li>3.50: rank 4 (next distinct).</li>
</ul>

This shows dense ranking of scores.

Understanding the Problem

How do you solve LeetCode 178: Rank Scores in SQL? We need to:

Assign a rank to each score based on descending order.
Use dense ranking: ties share a rank, next distinct score gets the next rank (e.g., 1,1,2, not 1,1,3).
Return all rows with score and rank, ordered by score descending.

For [3.50, 3.65, 4.00, 3.85, 4.00, 3.65], sort to [4.00, 4.00, 3.85, 3.65, 3.65, 3.50], then rank as 1, 1, 2, 3, 3, 4. We need an efficient query, ideally leveraging SQL window functions for simplicity. This isn’t a Python coding task like LeetCode 174: Dungeon Game; it’s about SQL ranking. We’ll use: 1. Dense Rank Window Function: Efficient, elegant—our best solution. 2. Self Join with Count: Alternative approach.

Let’s dive into the best solution.

Best Solution: Dense Rank Window Function Approach

Explanation

Dense Rank Window Function uses SQL’s DENSE_RANK() to assign ranks:

Select score and apply DENSE_RANK() over scores ordered descending.
DENSE_RANK() assigns consecutive ranks to distinct values, ties get the same rank.
Order the result by score descending.

This ensures O(n log n) time (database sorting, often optimized), O(1) extra space beyond result, and simplicity with modern SQL support (e.g., MySQL 8.0+).

For [3.50, 3.65, 4.00, 3.85, 4.00, 3.65]:

DENSE_RANK() → [1,3,1,2,1,3], result matches example.

Step-by-Step Example

Example: Scores = [3.50, 3.65, 4.00, 3.85, 4.00, 3.65]

Goal: Return table with score and rank.

Step 1: Select all scores.

Rows: 3.50, 3.65, 4.00, 3.85, 4.00, 3.65.

Step 2: Apply DENSE_RANK() over descending order.

Sort: [4.00, 4.00, 3.85, 3.65, 3.65, 3.50].
Ranks: [1, 1, 2, 3, 3, 4].

Step 3: Order by score descending.

Result:

4.00, 1
4.00, 1
3.85, 2
3.65, 3
3.65, 3
3.50, 4

Finish: Return the ranked table.

How the Code Works (Dense Rank Window Function) – Detailed Line-by-Line Explanation

Here’s the SQL query with a thorough breakdown:

-- Line 1: Select columns
SELECT 
    score, 
    -- Score column (e.g., 3.50, 3.65, etc.)

    -- Line 2: Dense rank calculation
    DENSE_RANK() OVER (
        ORDER BY score DESC
        -- Rank scores descending (e.g., 4.00→1, 3.85→2)
    ) AS 'rank'
    -- Alias as 'rank' (e.g., 1, 2, 3, 4)

-- Line 3: Source table
FROM 
    Scores
    -- Base table (e.g., all scores)

-- Line 4: Order result
ORDER BY 
    score DESC
    -- Ensure descending order (e.g., 4.00, 3.85, 3.65, 3.50)

This detailed breakdown clarifies how the dense rank window function efficiently ranks scores.

Alternative: Self Join with Count Approach

Explanation

Self Join with Count ranks scores by counting higher distinct scores:

Select each score from Scores (s1).
Join with Scores (s2) to count distinct scores ≥ s1.score.
Rank = count of distinct higher scores + 1.
Order by score descending.

It’s a practical alternative, O(n²) time without indexing (optimized with indexing), O(n) space for join results, but more complex and less efficient than window functions, working on older SQL systems.

For [3.50, 3.65, 4.00, 3.85, 4.00, 3.65]:

4.00: 0 higher → rank 1.
3.85: 1 higher (4.00) → rank 2.
3.65: 2 higher (4.00, 3.85) → rank 3.
3.50: 3 higher → rank 4.

Step-by-Step Example (Alternative)

For the same example:

Score 4.00: COUNT(DISTINCT s2.score WHERE s2.score >= 4.00) = 1 (self), rank = 1.
Score 3.85: COUNT(...) = 2 (4.00, 3.85), rank = 2.
Score 3.65: COUNT(...) = 3 (4.00, 3.85, 3.65), rank = 3.
Score 3.50: COUNT(...) = 4, rank = 4.
Order: Matches example output.

How the Code Works (Self Join)

SELECT 
    s1.score, 
    (SELECT COUNT(DISTINCT s2.score) 
     FROM Scores s2 
     WHERE s2.score >= s1.score) AS 'rank'
FROM 
    Scores s1
ORDER BY 
    s1.score DESC

Complexity

Dense Rank Window Function:

Time: O(n log n) – sorting (optimized by database).
Space: O(1) – minimal extra space.

Self Join with Count:

Time: O(n²) – self-join and count (optimized with indexing).
Space: O(n) – join result.

Efficiency Notes

Dense Rank Window Function is the best solution with O(n log n) time (often optimized), O(1) space, offering simplicity and efficiency with modern SQL—Self Join with Count uses O(n²) time (less efficient without optimization) and O(n) space, making it portable but slower and more complex.

Key Insights

DENSE_RANK: Consecutive ranks.
Self Join: Counts higher scores.
Ties: Handled naturally.

Additional Example

Scores:
| 1 | 90 |
| 2 | 90 |
| 3 | 85 |
Output:
| 90 | 1 |
| 90 | 1 |
| 85 | 2 |
Explanation: 90 (1st), 85 (2nd).

Edge Cases

Single Score: [100] → [100,1].
All Ties: [100,100] → [100,1],[100,1].
Multiple Ties: [90,90,80,80] → [90,1],[90,1],[80,2],[80,2].

Both solutions handle these well.

Final Thoughts

LeetCode 178: Rank Scores in SQL is a rewarding ranking challenge. The Dense Rank Window Function solution excels with its efficiency and elegance, while Self Join with Count offers a traditional approach. Want more SQL? Try LeetCode 177: Nth Highest Salary for Nth ranking or explore Python Basics for coding skills. Ready to practice? Solve LeetCode 178 on LeetCode with [3.50, 3.65, 4.00, 3.85, 4.00, 3.65], aiming for the ranked result—test your skills now!