Chapter 1: Introduction to the Contest Leaderboard Problem

If you aim to enhance your SQL expertise or prepare for your upcoming technical interviews, consistent practice is essential. Platforms like HackerRank provide numerous challenges to sharpen your skills. In this article, we will delve into the Contest Leaderboard challenge and how to effectively address it.

Problem Overview

Julia was impressed by your assistance in her previous coding contest, and she now seeks your help with a new challenge! The task is to calculate the total score for each hacker, which is derived from their highest scores across all challenges. Your objective is to write a query that returns the hacker_id, name, and total score, sorted by score in descending order. If multiple hackers have the same total score, arrange them by hacker_id in ascending order. Additionally, exclude hackers who have a total score of zero.

The sample input provided by HackerRank consists of two tables: hackers and submissions.

#### Hackers Table

#### Submissions Table

Solution Steps

To solve this problem, we need to create a query that retrieves the hacker ID, name, and total score for each hacker. If a hacker has multiple submissions for a single challenge, only their highest score should be accounted for. Moreover, hackers with a total score of zero will be omitted from the final results.

Our first step is to compute the maximum score achieved by each hacker for every challenge:

SELECT

h.hacker_id,

h.name,

s.challenge_id,

MAX(score) AS max_score

FROM

hackers h

INNER JOIN submissions s ON h.hacker_id = s.hacker_id

GROUP BY

h.hacker_id,

h.name,

s.challenge_id

Next, we refine our query to sum the maximum scores for each hacker while excluding those with zero scores:

SELECT

hacker_id,

name,

SUM(max_score) AS total_score

FROM (

SELECT

h.hacker_id,

h.name,

s.challenge_id,

MAX(score) AS max_score

FROM

hackers h

INNER JOIN submissions s ON h.hacker_id = s.hacker_id

GROUP BY

h.hacker_id,

h.name,

s.challenge_id

) AS x

GROUP BY

hacker_id,

name

HAVING SUM(max_score) <> 0

ORDER BY

total_score DESC,

hacker_id

Conclusion

In this brief tutorial, we have tackled the Contest Leaderboard SQL problem on HackerRank. I hope this guide was clear and helpful. Remember to keep practicing!

A detailed walkthrough of the Contest Leaderboard problem on HackerRank, illustrating step-by-step SQL solutions.

Explore the subquery version of the Contest Leaderboard problem, enhancing your SQL skills with practical examples.

Thank you for being part of our community! Before you leave, consider engaging with the content and following the author to stay updated on more insightful articles.