The correct one :::

SELECT H.hacker_id, H.name FROM Hackers AS H JOIN Submissions AS S ON H.hacker_id = S.hacker_id JOIN Challenges AS C ON S.challenge_id = C.challenge_id JOIN Difficulty AS D ON C.difficulty_level = D.difficulty_level WHERE S.score = D.score GROUP BY H.hacker_id, H.name HAVING COUNT(DISTINCT S.challenge_id) > 1 ORDER BY COUNT(DISTINCT S.challenge_id) DESC, H.hacker_id ASC;

Correct relationship path (used by working queries): Hackers (h) —(h.hacker_id = s.hacker_id)—> Submissions (s) —(s.challenge_id = c.challenge_id)—> Challenges (c) —(c.difficulty_level = d.difficulty_level)—> Difficulty (d) Your path: Hackers (H) —(H.hacker_id = C.hacker_id)—> Challenges (C) —(C.difficulty_level = D.difficulty_level)—> Difficulty (D) —(C.challenge_id = S.challenge_id)—> Submissions (S)

I was using a different story of joinsas i got inspired by the answer here but did not give full understanding. I also thought and connecting Hackers and Submissions will work but missed the point of MAX score

My solution:

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 c.difficulty_level = d.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

what is wrong in mine?

with cte as ( select sub.submission_id as submission_id, sub.hacker_id as hacker_id, hack.name as name, sub.challenge_id as challenge_id, diff.difficulty_level as difficulty_level, sub.score as score_gain, diff.score as total_score

 from Submissions sub 
left join Hackers hack on hack.hacker_id = sub.hacker_id 
left join Challenges chal on chal.challenge_id = sub.challenge_id
left join Difficulty diff on diff.difficulty_level = chal.difficulty_level

) select hacker_id,name from cte

where score_gain=total_score group by hacker_id having count(challenge_id)>1 order by count(challenge_id) desc,name asc

SELECT CONCAT(Hackers.hacker_id," ",Hackers.name) FROM Hackers INNER JOIN Submissions ON Hackers.hacker_id = Submissions.hacker_id INNER JOIN Challenges ON Submissions.challenge_id = Challenges.challenge_id INNER JOIN Difficulty ON Challenges.difficulty_level = Difficulty.difficulty_level WHERE Submissions.score = Difficulty.score GROUP BY Hackers.hacker_id , Hackers.name HAVING COUNT(DISTINCT Submissions.challenge_id) >1 ORDER BY COUNT(DISTINCT Challenges.challenge_id) DESC , Hackers.hacker_id ASC;

ORACLE

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 c.difficulty_level = d.difficulty_level
Where s.score = d.score
Group by h.hacker_id, h.name
Having count(distinct(s.challenge_id)) > 1
Order by count(distinct(c.challenge_id)) desc, h.hacker_id asc;