SELECT h.hacker_id, h.name FROM Hackers h JOIN Submissions s ON h.hacker_id = s.hacker_id JOIN Challenges c ON s.challenge_id = c.challenge_id JOIN Difficulty d on d.difficulty_level = c.difficulty_level WHERE s.score = d.score GROUP BY h.hacker_id, h.name HAVING COUNT(DISTINCT c.challenge_id) > 1 ORDER BY COUNT(DISTINCT c.challenge_id) DESC, h.hacker_id ASC;

MS SQL SERVER:

SELECT Submissions.hacker_id as Hacker_ID, Count(Submissions.hacker_id) as Count_full INTO hackers1996 FROM Submissions JOIN Challenges ON Submissions.challenge_id=Challenges.challenge_id JOIN Difficulty ON Challenges.difficulty_level=Difficulty.difficulty_level WHERE Submissions.score=Difficulty.score GROUP BY Submissions.hacker_id HAVING Count(Submissions.hacker_id)>1 ORDER BY Count(Submissions.hacker_id) DESC,Submissions.hacker_id ASC; SELECT Concat((hackers1996.Hacker_ID),' ',(Hackers.name)) FROM hackers1996 JOIN Hackers ON hackers1996.Hacker_ID=Hackers.Hacker_ID ORDER BY hackers1996.Count_full DESC, hackers1996.Hacker_ID ASC;

MS SQL

select z.hacker_id, h.name from (select v.hacker_id, sum(v.czy_max)as ile
from (select x., case when x.score = x.score_max then 1 else 0 end as czy_max from (select s.hacker_id, s.score, d.score as score_max from submissions s join challenges c on c.challenge_id = s.challenge_id join difficulty d on d.difficulty_level = c.difficulty_level )x )v group by v.hacker_id )z join hackers h on z.hacker_id = h.hacker_id and z.ile>1 order by z.ile desc, z.hacker_id asc;*

Short-hand columns:

SELECT
    h.hacker_id,
    h.name
FROM hackers h
join submissions s on s.hacker_id = h.hacker_id
join challenges c on c.challenge_id = s.challenge_id
join difficulty d on d.difficulty_level = c.difficulty_level and d.score = s.score
group by 1,2
having count(s.challenge_id) > 1
order by count(s.challenge_id) desc, 1;

select concat(h.hacker_id, ' ',h.name) from hackers h JOIN Submissions s on h.hacker_id = s.hacker_id JOIN Challenges c on c.challenge_id = s.challenge_id JOIN Difficulty d on c.difficulty_level = d.difficulty_level where s.score = d.score group by h.hacker_id , h.name having count(s.challenge_id) > 1 order by count(s.challenge_id) desc, h.hacker_id;