Hackerank challenges MYSQL query returns wrong output

From this hackerank problem https://www.hackerrank.com/challenges/challenges/problem?isFullScreen=true

Why won't my query return all the names of the hackers who have completed the maximum number of challenges?

My query gives all the names and all the challanges each hacker has done.

This is my query down below


SELECT am.name,am.names_appeared from (

    SELECT Hackers.hacker_id,Hackers.name,Count(name) as names_appeared from Hackers 
    INNER JOIN Challenges on Hackers.hacker_id = Challenges.hacker_id GROUP BY Hackers.hacker_id,Hackers.name
    
)am
GROUP BY am.name,am.names_appeared
HAVI开发者_如何学PythonNG am.names_appeared = MAX(am.names_appeared)

`

`

Add a rank to your inner query and keep only rank = 1 in your outer query.

SELECT am.name,am.names_appeared from (

    SELECT Hackers.hacker_id,
           Hackers.name,
           Count(name) as names_appeared,
       rank() Over (Partition By Hackers.hacker_id,Hackers.name
        Order by Count(name) Desc) as hackerrank
    from Hackers 
      INNER JOIN Challenges
        on Hackers.hacker_id = Challenges.hacker_id
    GROUP BY Hackers.hacker_id,Hackers.name

)am
XXXX lose this line XXXX Group BY am.name,am.names_appeared
XXXX lose this line XXXX HAVING am.names_appeared = MAX(am.names_appeared)
Where hackerank=1