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.