HackerRank - Top Competitors

SELECT Hack.hacker_id, Hack.name FROM 
Hackers Hack, Challenges Cha, Submissions Sub, Difficulty Diff
WHERE Hack.hacker_id = Cha.hacker_id
AND Cha.hacker_id = Sub.hacker_id
AND Cha.challenge_id = Sub.challenge_id
AND Cha.difficulty_level = Diff.difficulty_level
AND Sub.score = Diff.score
GROUP BY Hack.hacker_id, Hack.name
HAVING COUNT(Hack.hacker_id) > 1
ORDER BY COUNT(Hack.hacker_id) DESC, Hack.hacker_id;

왜 안되지 계속 생각했는데

WHERE Hack.hacker_id = Cha.hacker_id
AND Cha.hacker_id = Sub.hacker_id

여기가 문제였음

Challenges의 hacker_id는 챌린지를 만든 해커의 id이다.

여기서는 점수를 비교할꺼기 때문에 저 hacker_id는 필요가없다.

Submissions <==> Hackers

Submissions <==> Challenges

이렇게 엮어주면 된다.

SELECT Hack.hacker_id, Hack.name FROM 
Hackers Hack, Challenges Cha, Submissions Sub, Difficulty Diff
WHERE Hack.hacker_id = Sub.hacker_id
AND Cha.challenge_id = Sub.challenge_id
AND Cha.difficulty_level = Diff.difficulty_level
AND Sub.score = Diff.score
GROUP BY Hack.hacker_id, Hack.name
HAVING COUNT(Hack.hacker_id) > 1
ORDER BY COUNT(Hack.hacker_id) DESC, Hack.hacker_id;

통과~