HackerRank Solution Explanation- Challenges

This is a brief explanation of the Challenges | HackerRank. This is a beginner’s guide, to how to think and build a solution step by step from chunks.

Connect with me on LinkedIn

STEP 1: Gather the basic information.

1. Print the hacker_id, name, and the total number of challenges created by each student.
2. Sort your results by the total number of challenges in descending order, then sort the result by hacker_id.

Write a basic SQL query on the basis of given info:

SELECT h.hacker_id, h.name, COUNT(c.challenge_id) AS cnt
FROM hackers h
JOIN challenges c ON c.hacker_id = h.hacker_id
GROUP BY c.hacker_id, h.name
ORDER BY cnt DESC, h.hacker_id;

If more than one student created the same number of challenges and the count is less than the maximum number of challenges created, then exclude those students from the result.

There is one more condition :

If more than one student created the same number of challenges and the count is less than the maximum number of challenges created, then exclude those students from the result.

Now acc to this condition, we have to find out 2 things:

1. Find MAX(c.challenge_id).
2. if cnt < MAX(c.challenge_id) => do not print

Find MAX(c.challenge_id) .

SELECT  hacker_id, COUNT(challenge_id) AS challenge_count
        FROM challenges
        GROUP BY hacker_id

Output of above query

Now we got the count of challenges performed by individual students. Find max among all. (here , 6 is the count).

→ Output :6

  SELECT MAX(challenge_count)
    FROM (
        SELECT COUNT(challenge_id) AS challenge_count
        FROM challenges
        GROUP BY hacker_id
    )

If cnt < MAX(c.challenge_id) => do not print.

Condition: 2 diff hackers(students) have same count. i.e.

1. h.hacker_id <> h1.hacker_id
2. cnt1 = cnt
3. cnt1 < max_counts

    SELECT COUNT(c1.challenge_id) AS cnt1
    FROM hackers h1
    JOIN challenges c1 ON c1.hacker_id = h1.hacker_id
    where h.hacker_id <> h1.hacker_id
    GROUP BY c1.hacker_id, h1.name
    HAVING cnt1 = cnt and  cnt1<(
    SELECT MAX(challenge_count)
    FROM (
        SELECT COUNT(challenge_id) AS challenge_count
        FROM challenges
        GROUP BY hacker_id
    ) AS max_counts
    )

Final solution: Combining these 3 chunks.

SELECT h.hacker_id, h.name, COUNT(c.challenge_id) AS cnt
FROM hackers h
JOIN challenges c ON c.hacker_id = h.hacker_id
GROUP BY c.hacker_id, h.name
HAVING NOT EXISTS (
    SELECT COUNT(c1.challenge_id) AS cnt1
    FROM hackers h1
    JOIN challenges c1 ON c1.hacker_id = h1.hacker_id
    where h.hacker_id <> h1.hacker_id
    GROUP BY c1.hacker_id, h1.name
    HAVING cnt1 = cnt and  cnt1<(
    SELECT MAX(challenge_count)
        FROM (
            SELECT COUNT(challenge_id) AS challenge_count
            FROM challenges
            GROUP BY hacker_id
        ) AS max_counts 
    ) -- cnt1 < max_counts
)
ORDER BY cnt DESC, h.hacker_id;

SCENERIO 1:

Gets converted to

FINAL OUTPUT

SCENERIO 2:

No Change as [ cnt1 < max_counts ] is false.

Connect with me on LinkedIn .