SELECT A.Hacker_id,D.Name From Submissions A JOIN Challenges B ON A.challenge_id=B.challenge_id JOIN Difficulty C ON B.Difficulty_level=C.Difficulty_level JOIN Hackers D ON A.Hacker_id=D.Hacker_id where C.score=A.score group by A.Hacker_id, D.Name having count(A.challenge_id)>1 order by count(A.challenge_id) desc, A.Hacker_id asc

SELECT A.Hacker_id,D.Name From Submissions A JOIN Challenges B ON A.challenge_id=B.challenge_id JOIN Difficulty C ON B.Difficulty_level=C.Difficulty_level JOIN Hackers D ON A.Hacker_id=D.Hacker_id where C.score=A.score

group by A.Hacker_id, D.Name having count(A.challenge_id)>1 order by count(A.challenge_id) desc, A.Hacker_id asc

/* Enter your query here. */ SELECT hacker_id, name FROM( SELECT a.hacker_id, a.name, COUNT(DISTINCT CASE WHEN b.score = d.score THEN b.challenge_id else NULL END) AS total_perfect_score FROM Submissions b LEFT JOIN Challenges c ON b.challenge_id = c.challenge_id LEFT JOIN Difficulty d ON c.difficulty_level = d.difficulty_level LEFT JOIN Hackers a ON b.hacker_id = a.hacker_id GROUP BY 1,2) base WHERE total_perfect_score >1 ORDER BY total_perfect_score DESC, hacker_id

SELECT h.hacker_id, h.name FROM Hackers h JOIN ( SELECT DISTINCT s.hacker_id, s.challenge_id FROM Submissions s 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 ) AS full_scores ON h.hacker_id = full_scores.hacker_id GROUP BY h.hacker_id, h.name HAVING COUNT(full_scores.challenge_id) > 1 ORDER BY COUNT(full_scores.challenge_id) DESC, h.hacker_id;

Oracle option

WITH scores AS (
SELECT 
    s.hacker_id, h.name,
    count(*) total
FROM submissions s
inner join hackers h on h.hacker_id = s.hacker_id
inner join challenges c on c.challenge_id = s.challenge_id
inner join difficulty d on d.difficulty_level = c.difficulty_level
where d.score = s.score
having count(*) > 1
group by s.hacker_id, h.name)
select hacker_id, name from scores
order by total desc, hacker_id asc
;