Skip to content

Prepare For Coder Interview – Denny

  • Basic
  • Medium
  • Hard
  • Architect
  • Life

LeetCode: Rank Scores

Posted on February 12, 2018July 26, 2020 by braindenny

Rank Scores



Similar Problems:

  • CheatSheet: SQL & MySql
  • CheatSheet: Leetcode For Code Interview
  • CheatSheet: Common Code Problems & Follow-ups
  • Tag: #sql

Write a SQL query to rank scores. If there is a tie between two scores, both should have the same ranking. Note that after a tie, the next ranking number should be the next consecutive integer value. In other words, there should be no “holes” between ranks.

+----+-------+
| Id | Score |
+----+-------+
| 1  | 3.50  |
| 2  | 3.65  |
| 3  | 4.00  |
| 4  | 3.85  |
| 5  | 4.00  |
| 6  | 3.65  |
+----+-------+

For example, given the above Scores table, your query should generate the following report (order by highest score):

+-------+------+
| Score | Rank |
+-------+------+
| 4.00  | 1    |
| 4.00  | 1    |
| 3.85  | 2    |
| 3.65  | 3    |
| 3.65  | 3    |
| 3.50  | 4    |
+-------+------+

Github: code.dennyzhang.com

Credits To: leetcode.com

Leave me comments, if you have better ways to solve.


  • Solution: Add rank and prev for group id
## https://code.dennyzhang.com/rank-scores
select Score,
     @rank := @rank + (@prev <> (@prev := Score)) as Rank
from Scores, 
    (select @rank := 0, @prev := -1) as rows
order by Score desc

  • Solution: Add rank and prev for group id
## https://code.dennyzhang.com/rank-scores
select Score, CAST(Rank as UNSIGNED) as Rank
from
    (select Score,
         @rank := case when Score = @prev then @rank else @rank+1 end as Rank,
         @prev := Score as prev
    from Scores, 
        (select @rank := 0, @prev := -1) as rows
    order by Score desc) as tt

  • Solution 2
## https://code.dennyzhang.com/rank-scores
select ranking.Score as Score, ranking.Ranking as Rank
    from Scores inner join
      (select t1.Score as Score, count(1) as Ranking
        from
            (select distinct Score from Scores) as t1 inner join
            (select distinct Score from Scores) as t2
        where t1.Score <= t2.Score
        group by t1.Score) as ranking
    on Scores.Score = ranking.Score
    order by ranking.Score desc
linkedin
github
slack

Post Views: 4
Posted in MediumTagged sql

Post navigation

LeetCode: Tenth Line
LeetCode: Swap Salary

Leave a Reply Cancel reply

Your email address will not be published.

Tags

#array #backtracking #bfs #binarytree #bitmanipulation #blog #classic #codetemplate #combination #dfs #dynamicprogramming #game #graph #greedy #heap #inspiring #interval #linkedlist #manydetails #math #palindrome #recursive #slidingwindow #stack #string #subarray #trie #twopointer #twosum binarysearch editdistance hashmap intervaldp knapsack monotone oodesign presum rectangle redo review rotatelist series sql treetraversal unionfind

Recent Posts

  • a
  • a
  • a
  • a
  • a

Recent Comments

    Archives

    Categories

    • Amusing
    • Basic
    • Easy
    • Hard
    • Life
    • Medium
    • Resource
    • Review
    • Series
    • Uncategorized
    Proudly powered by WordPress | Theme: petals by Aurorum.