Overview of the "Challenges" Problem on HackerRank

Now, let's dive into the specifics of the problem definition. Julia assigned her students to develop coding challenges. The objective is to write a query that displays the hacker_id, name, and the total number of challenges created by each student. The results should be sorted by the total number of challenges in descending order. In cases where multiple students have created the same number of challenges, the sorting should then be done by hacker_id. Furthermore, if several students created the same number of challenges and that number is less than the maximum created, those students should be excluded from the results.

HackerRank provides two sample inputs and outputs. Below is the first set of input and output:

-- Hackers table

-- Challenges table

-- Sample Output

Here is the second example of input and output:

-- Hackers table

-- Challenges table

-- Sample Output

Formulating the SQL Query

The task is to write a query that retrieves the hacker_id, name, and challenges_created for each student. The results must be sorted in descending order by challenges_created and then in ascending order by hacker_id. Additionally, students who do not have the maximum challenges_created should be excluded unless they are the only ones with that count.

To start, we can determine the number of challenges created by each student without any exclusions:

SELECT

h.hacker_id,

h.name,

COUNT(c.challenge_id) challenges_created

FROM

hackers h INNER JOIN challenges c ON h.hacker_id = c.hacker_id

GROUP BY

h.hacker_id,

h.name

ORDER BY

3 DESC,

1

This query will yield the following results for the provided sample inputs:

-- Result for Sample Output 0

-- Result for Sample Output 1

Next, we need to filter out students who do not have a unique number of challenges created unless that number corresponds to the maximum challenges created by a single student. For the first sample input, Rose and Frank will be excluded since both have created 4 challenges while the maximum is 6. For the second sample output, both Rose and Angela will be retained since they have the highest count.

To determine the number of students who created the same number of challenges, we will add another query that groups the results from the first query by challenges_created:

WITH all_students AS (

SELECT

h.hacker_id,

h.name,

COUNT(c.challenge_id) challenges_created

FROM

hackers h INNER JOIN challenges c ON h.hacker_id = c.hacker_id

GROUP BY

h.hacker_id,

h.name

ORDER BY

3 DESC,

1

)

SELECT

challenges_created,

COUNT(*) num_students_created_this_chall

FROM

all_students

GROUP BY

challenges_created

This query will produce the following records for each sample input:

-- Result for Sample Output 0

-- Result for Sample Output 1

Now that we have these intermediate results, we can combine them and filter to include only those students who have either a unique number of challenges or have created the maximum number of challenges. Here’s the complete solution to the Challenges problem on HackerRank based on the outlined interpretation:

WITH all_students AS (

SELECT

h.hacker_id,

h.name,

COUNT(c.challenge_id) challenges_created

FROM

hackers h INNER JOIN challenges c ON h.hacker_id = c.hacker_id

GROUP BY

h.hacker_id,

h.name

ORDER BY

3 DESC,

1

),

num_students_per_num_challenges AS (

SELECT

challenges_created,

COUNT(*) num_students_created_this_chall

FROM

all_students

GROUP BY

challenges_created

)

SELECT

a.hacker_id,

a.name,

a.challenges_created

FROM

all_students a INNER JOIN num_students_per_num_challenges sc

ON a.challenges_created = sc.challenges_created

WHERE

sc.num_students_created_this_chall = 1

OR a.challenges_created = (SELECT MAX(challenges_created) FROM all_students)

Final Thoughts

In this brief tutorial, we've explored how to approach the Challenges problem on HackerRank and demonstrated a straightforward solution that yields the desired results. I hope this article has been helpful and easy to understand! Keep practicing and attempting as many challenges as possible.