Algorithm


Problem Name: Sql - Contest Leaderboard

Problem Link: https://www.hackerrank.com/challenges/contest-leaderboard/problem?isFullScreen=true

In this HackerRank Functions in SQL problem solution,

You did such a great job helping Julia with her last coding contest challenge that she wants you to work on this one, too!

The total score of a hacker is the sum of their maximum scores for all of the challenges. Write a query to print the hacker_id, name, and total score of the hackers ordered by the descending score. If more than one hacker achieved the same total score, then sort the result by ascending hacker_id. Exclude all hackers with a total score of 0 from your result.

Input Format

The following tables contain contest data:

  • Hackers: The hacker_id is the id of the hacker, and name is the name of the hacker.

  • Submissions: The submission_id is the id of the submission, hacker_id is the id of the hacker who made the submission, challenge_id is the id of the challenge for which the submission belongs to, and score is the score of the submission.

Sample Input

Hackers Table:

Submissions Table:

Sample Output

4071 Rose 191
74842 Lisa 174
84072 Bonnie 100
4806 Angela 89
26071 Frank 85
80305 Kimberly 67
49438 Patrick 43

 

 

 

Code Examples

#1 Code Example with SQL

Code - SQL


SELECT h.hacker_id, h.name, t1.total_score
  FROM (
        SELECT hacker_id, SUM(max_score) AS total_score
          FROM (
                SELECT hacker_id, MAX(score) AS max_score
                  FROM Submissions
                GROUP BY hacker_id, challenge_id
               ) t
        GROUP BY hacker_id
       ) t1
  JOIN Hackers h
    ON h.hacker_id = t1.hacker_id
 WHERE t1.total_score <> 0
 ORDER BY total_score DESC, hacker_id;
Copy The Code & Try With Live Editor
Advertisements

Demonstration


Previous
[Solved] Challenges in SQL solution in Hackerrank
Next
[Solved] SQL Project Planning in SQL solution in Hackerrank