LeetCode 178- Rank Scores (SQL & Python) Solutions
This article is going to provide the solution to LeetCode 178 Rank Scores. It’s a medium difficulty question.
Write a solution to find the rank of the scores. The ranking should be calculated according to the following rules:
- The scores should be ranked from the highest to the lowest.
- If there is a tie between two scores, both should have the same ranking.
- After a tie, the next ranking number should be the next consecutive integer value. In other words, there should be no holes between ranks.
Return the result table ordered by score in descending order.
The result format is in the following example.
If you want to skip the article, check out the full YouTube video where I code the answer live.
LeetCode 178 SQL Solution
with score_rank as (  SELECT  score,  DENSE_RANK() OVER (Order by score DESC) as rank  from Scores)SELECT score, rankfrom score_rankorder by rankThe code starts with a Common Table Expression (CTE) named “score_rank”. This CTE selects the “score” column from the “Scores” table and calculates a rank for each score using the DENSE_RANK() window function.
 The DENSE_RANK() function assigns a unique rank to each distinct score, starting from 1 for the highest score, 2 for the second-highest, and so on, without any gaps in the ranking.
After defining the CTE, the main query selects the “score” and “rank” columns from the “score_rank” CTE. It does not perform any additional calculations but simply retrieves the score and its corresponding rank from the CTE.
Finally, the result set is ordered by the “rank” column in ascending order. This ensures that the scores are displayed in the order of their ranks, from lowest to highest.
LeetCode 178 Python Solution
import pandas as pddef order_scores(scores: pd.DataFrame) -> pd.DataFrame:Â Â scores['rank'] = scores['score'].rank(method='dense', ascending=False)Â Â scores2 = scores.sort_values(by='score', ascending=False)Â Â return scores2[['score', 'rank']]The order_scores function uses the rank() method available in Pandas to calculate the rank of each score in the ‘score’ column.Â
The method=’dense’ parameter specifies that dense ranking should be used, similar to the SQL DENSE_RANK() function. The ascending=False parameter ensures that higher scores receive lower ranks. This results in a new column named ‘rank’ in the scores DataFrame.
The function sorts the DataFrame scores by the ‘score’ column in descending order (ascending=False) using the sort_values() method. This step ensures that the scores are ordered from highest to lowest.
Finally, the function returns a new DataFrame scores2 containing only the ‘score’ and ‘rank’ columns, ordered by score in descending order.
