Hackerrank, challenges problem. Why does the query return unique output of count (challenges)?

49 views Asked by At

Here is the link for the problem: Hackerrannk-challenges

One of the solution:

SELECT 
    c.hacker_id, h.name, COUNT(c.challenge_id) AS cnt 
FROM 
    Hackers AS h 
JOIN 
    Challenges AS c ON h.hacker_id = c.hacker_id
GROUP BY 
    c.hacker_id, h.name 
HAVING 
    cnt = (SELECT COUNT(c1.challenge_id) 
           FROM Challenges AS c1 
           GROUP BY c1.hacker_id 
           ORDER BY count(*) DESC
           LIMIT 1)
    OR cnt NOT IN (SELECT COUNT(c2.challenge_id) 
                   FROM Challenges AS c2 
                   GROUP BY c2.hacker_id 
                   HAVING c2.hacker_id <> c.hacker_id)
ORDER BY 
    cnt DESC, c.hacker_id;

I can not understand why this part returns unique outputs:

cnt NOT IN (SELECT count(c2.challenge_id) 
            FROM Challenges AS c2 
            GROUP BY c2.hacker_id 
            HAVING c2.hacker_id <> c.hacker_id)

I'm especially interested how HAVING c2.hacker_id <> c.hacker_id works.

0

There are 0 answers