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.
STEP 1: Gather the basic information.
- Print the hacker_id, name, and the total number of challenges created by each student.
- 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;
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:
- Find MAX(c.challenge_id).
- 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
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.
- h.hacker_id <> h1.hacker_id
- cnt1 = cnt
- 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;