Contest Leaderboard | HackerRank | MSSQL

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