Rank Scores SQL Challenge - PostgreSQL Solution

1. Introduction

In this post, we'll delve into ranking scores using PostgreSQL. This operation is a common requirement in databases where you need to assign a rank to each row based on its value in comparison to others in the dataset.

Problem

The task is to write a PostgreSQL query that ranks each score in a table. The rank should be determined based on the score, with the highest score getting the rank of 1.

Database Table:

Scores Table:

id	score
1	100
2	95
3	90

3. PostgreSQL Solution

SELECT
    id,
    score,
    RANK() OVER (ORDER BY score DESC) as rank
FROM Scores;

Output:

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

Explanation:

1. The query uses the RANK() window function to assign a rank to each score.

2. The scores are ordered in descending order.

3. RANK() assigns a unique rank to each distinct score, with ties receiving the same rank and creating gaps in the rank sequence.

4. This method is useful for ranking items that may have tied scores.