Why shouldn't consider the field hacker_id in the JOIN between the Submissions table and Challenges table? If I only consider the challenge_id I wouldn't cause duplicates? Since that the Challenges table has the Challenges by each Hacker.

Is this question doesnot support CTE ?? Becaz when i used to solve the query using CTE , i got the syntax error near first line .... Clear me where is the syntax error or if not then does this ques support CTE or not ?

WITH s_data AS ( SELECT s.challenge_id, s.hacker_id, c.difficulty_level, d.score AS d_score, s.score AS s_score, h.name FROM challenges c JOIN difficulty d ON d.difficulty_level = c.difficulty_level JOIN submissions s ON s.challenge_id = c.challenge_id JOIN hackers h ON h.hacker_id = s.hacker_id ), full_score AS ( SELECT challenge_id, hacker_id, s_score, name, d_score FROM s_data WHERE s_score = d_score ) SELECT hacker_id, name FROM full_score GROUP BY hacker_id, name HAVING COUNT() > 1 ORDER BY COUNT() DESC, hacker_id;

MySQL

SELECT Hac.hacker_id,Hac.name FROM Submissions AS Sub 
JOIN Hackers AS Hac USING(hacker_id)
JOIN Challenges AS Cha USING (challenge_id) 
JOIN Difficulty AS Dif USING(difficulty_level)
WHERE Sub.score=Dif.score
GROUP BY Hac.hacker_id,Hac.name
HAVING COUNT(*) > 1
ORDER BY COUNT(*) DESC ,Hac.hacker_id

WITH result AS ( SELECT h.hacker_id , h.name , s.challenge_id , s.score FROM Hackers h JOIN Submissions s ON h.hacker_id=s.hacker_id ) , result_1 AS ( SELECT r.hacker_id , r.name , r.challenge_id , c.difficulty_level , r.score FROM result r JOIN challenges c ON r.challenge_id = c.challenge_id ) , result_2 AS ( SELECT r1.hacker_id , r1.name , r1.challenge_id , r1.difficulty_level , r1.score FROM result_1 r1 JOIN Difficulty d ON r1.score = d.score AND r1.difficulty_level=d.difficulty_level ) , result_3 AS ( SELECT r2.* , COUNT (r2.hacker_id) OVER (PARTITION BY r2.hacker_id ORDER BY r2.hacker_id) as count FROM result_2 r2 )

SELECT r3.hacker_id , r3.name FROM result_3 r3 WHERE r3.count > 1 GROUP BY r3.hacker_id, r3.name, r3.count ORDER BY r3.count DESC, r3.hacker_id ASC

;with challenge_cte as ( select c.challenge_id, d.score as max_score from Challenges as c join Difficulty as d on d.difficulty_level = c.difficulty_level ), submission_cte as ( select s.hacker_id, COUNT(DISTINCT cte.challenge_id) as counter_challenge from Submissions as s left join challenge_cte as cte on cte.challenge_id = s.challenge_id where s.score = cte.max_score group by s.hacker_id HAVING COUNT(DISTINCT s.challenge_id) > 1 ) select h.hacker_id, h.name from Hackers as h join submission_cte as c on c.hacker_id = h.hacker_id order by c.counter_challenge DESC, h.hacker_id ASC