LeetCode 178. Rank Scores SQL Solution

Problem

LeetCode SQL Problem

178. Rank Scores

Scores table

id	score
1	3.5
2	3.65
3	4
4	3.85
5	4
6	3.65

Solution - Dense_Rank Approach

• The problem states that there should be no holes between ranks so we're using Dense_Rank() to sort score from highest to lowest.
• rank is a reverved word in MySQL so we need to escape it with an apostrophe before and after the keyword in order to use it as a column name.

MySQL

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

TSQL

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

Query Output

score	rank
4	1
4	1
3.85	2
3.65	3
3.65	3
3.5	4

Solution - Subquery Approach

• A subquery is used to find out how many distinct score values are greater than the current score value. Adding that value by 1 is the current score's rank.
• rank is a reverved word in MySQL so we need to escape it with an apostrophe before and after the keyword in order to use it as a column name.

MySQL

SELECT s.score,
	(
		SELECT count(DISTINCT (ss.score)) + 1
		FROM Scores ss
		WHERE ss.score > s.score
		) AS 'rank'
FROM Scores s
ORDER BY s.score DESC

TSQL

SELECT s.score,
	(
		SELECT count(DISTINCT (ss.score)) + 1
		FROM Scores ss
		WHERE ss.score > s.score
		) AS 'rank'
FROM Scores s
ORDER BY s.score DESC

Query Output

score	rank
4	1
4	1
3.85	2
3.65	3
3.65	3
3.5	4