The Problem
In this problem, we need to calculate the total score of each hacker, where the total score is defined as the sum of their maximum scores across all challenges. We then need to print the hacker_id, name, and total score of the hackers, sorted by descending total score and ascending hacker_id for ties. We need to exclude all hackers with a total score of 0.
The Input
The input consists of two tables:
Hackers Table: The hacker_id is the id of the hacker, and name is the name of the hacker.
Submissions Table: 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 and output are available for more in-depth understanding of the problem.
Sample Input
Hackers Table:
Submissions Table:
The Output
Sample Output:
4071 Rose 191 74842 Lisa 174 84072 Bonnie 100 4806 Angela 89 26071 Frank 85 80305 Kimberly 67 49438 Patrick 43 Explanation
Hacker 4071 submitted solutions for challenges 19797 and 49593, so the total score =95+max(43,96)=191.
Hacker 74842 submitted solutions for challenges 19797 and 63132, so the total score =max(98,5)+76=174.
Hacker 84072 submitted solutions for challenges 49593 and 63132, so the total score =100+0=100.
The total scores for hackers 4806, 26071, 80305, and 49438 can be similarly calculated.
The Solution
We'll discuss three SQL solutions, each with different strategies and trade-offs.
Source Code 1
The first source code creates a total_score Common Table Expression (CTE) that joins the Hackers and Submissions tables and calculates the maximum score per challenge for each hacker. It then sums these max scores per hacker and filters out hackers with a total score of 0. It orders the result by total score in descending order and hacker_id in ascending order for ties. This solution uses the OVER clause with PARTITION BY to calculate the max score per challenge per hacker, which simplifies the subsequent aggregation.
WITH total_score AS ( SELECT DISTINCT h.hacker_id, h.name, s.challenge_id, MAX(s.score) OVER (PARTITION BY h.hacker_id, s.challenge_id) AS max_score_per_subm FROM Hackers h JOIN Submissions s ON h.hacker_id = s.hacker_id ) SELECT hacker_id, name, SUM(max_score_per_subm) AS total FROM total_score GROUP BY hacker_id, name HAVING SUM(max_score_per_subm) > 0 ORDER BY total DESC, hacker_id Source Code 2
The second solution differs in that it first calculates the max score per challenge per hacker using a GROUP BY clause in the total_score CTE, then sums these max scores per hacker in the score_per_hacker CTE. This involves two separate groupings, which may increase execution time. However, it also separates concerns and can be easier to understand.
WITH total_score AS ( SELECT s.hacker_id, MAX(s.score) AS max_score FROM Submissions s GROUP BY s.hacker_id, s.challenge_id ), score_per_hacker AS ( SELECT ts.hacker_id, SUM(ts.max_score) AS total_score FROM total_score ts GROUP BY ts.hacker_id ) SELECT sp.hacker_id, h.name, sp.total_score FROM score_per_hacker sp JOIN Hackers h ON sp.hacker_id = h.hacker_id WHERE sp.total_score > 0 ORDER BY sp.total_score DESC, sp.hacker_id Source Code 3
The third solution uses the ROW_NUMBER() function to assign a unique rank to each submission by each hacker for each challenge. It then only includes the highest-ranked (i.e., highest-scoring) submission for each challenge in the total score. This strategy avoids the need to use DISTINCT in the CTE or to group by challenge_id, potentially improving performance.
SELECT hacker_id, name, SUM(CASE WHEN rn = 1 THEN score ELSE 0 END) AS total_score FROM ( SELECT h.hacker_id, h.name, s.score, ROW_NUMBER() OVER(PARTITION BY s.hacker_id, s.challenge_id ORDER BY s.score DESC) as rn FROM Hackers h JOIN Submissions s ON h.hacker_id = s.hacker_id ) t GROUP BY hacker_id, name HAVING SUM(CASE WHEN rn = 1 THEN score ELSE 0 END) > 0 ORDER BY total_score DESC, hacker_id Conclusion
All three solutions achieve the same result but use different techniques to calculate the total score of each hacker. The first solution is straightforward but might not be as efficient due to its use of DISTINCT. The second solution separates concerns by creating a CTE for each step, which could improve readability at the cost of execution time. The third solution is likely the most efficient due to its use of the ROW_NUMBER() function to select the highest score per challenge per hacker directly.
In summary, while different SQL queries can achieve the same result, their performance can vary significantly based on the SQL features and functions used. Therefore, it's crucial to consider different approaches and understand their trade-offs.
You can find the original problem at HackerRank.
For more insightful solutions and tech-related content, feel free to connect with me on my Beacons page.
ranggakd - Link in Bio & Creator Tools | Beacons
@ranggakd | center details summary summary Oh hello there I m a an Programmer AI Tech Writer Data Practitioner Statistics Math Addict Open Source Contributor Quantum Computing Enthusiast details center.
beacons.ai 
Top comments (0)