What's wrong with this syntax of Mysql 5.7.27-0?-CodePudding

The link to the problem: Challenges

Select h.hacker_id, h.name, COUNT(DISTINCT c.Challenge_id) as Cnt from Hackers h   
    INNER JOIN Challenges c ON h.Hacker_id = c.Hacker_id Having
    Cnt = (Select COUNT(DISTINCT Challenge_id) as c_cmp from Challenges   
    group by Hacker_id order by c_cmp desc LIMIT 1) 
    OR
    Cnt IN (Select final_cnt from (Select h.Hacker_id, h.Name, COUNT(DISTINCT c.Challenge_id) as final_cnt from Hackers 
    JOIN Challenges c ON h.Hacker_id = c.Hacker_id) as T group by final_cnt having COUNT(final_cnt) = 1)   
    order by Cnt desc, h.Hacker_id;

This compiles perfectly in MySQL workbench 8.0 but fails with Hackerrank's MySQL compiler. When I use the query Select version(), it returns:

5.7.27-0ubuntu0.18.04.1

Also the error message:

ERROR 1054 (42S22) at line 1: Unknown column 'h.Hacker_id' in 'field list'

What's wrong with the above syntax?

CodePudding user response：

Select h.hacker_id, h.name, COUNT(DISTINCT c.Challenge_id) as Cnt from Hackers h   
    INNER JOIN Challenges c ON h.Hacker_id = c.Hacker_id Having
    Cnt = (Select COUNT(DISTINCT Challenge_id) as c_cmp from Challenges   
    group by Hacker_id order by c_cmp desc LIMIT 1) 
    OR
    Cnt IN (Select final_cnt from (Select h.Hacker_id, h.Name, COUNT(DISTINCT c.Challenge_id) as final_cnt from Hackers [ADD ALIAS]
    JOIN Challenges c ON h.Hacker_id = c.Hacker_id) as T group by final_cnt having COUNT(final_cnt) = 1)   
    order by Cnt desc, h.Hacker_id;

you're missing an alias, check [ADD ALIAS]

CodePudding user response：

I think the alias is missing, try this way:

Select h.hacker_id, h.name, COUNT(DISTINCT c.Challenge_id) as Cnt from Hackers h
INNER JOIN Challenges c ON h.Hacker_id = c.Hacker_id Having Cnt = (Select COUNT(DISTINCT Challenge_id) as c_cmp from Challenges
group by Hacker_id order by c_cmp desc LIMIT 1) OR Cnt IN (Select final_cnt from (Select h.Hacker_id, h.Name, COUNT(DISTINCT c.Challenge_id) as final_cnt from Hackers h JOIN Challenges c ON h.Hacker_id = c.Hacker_id) as T group by final_cnt having COUNT(final_cnt) = 1)
order by Cnt desc, h.Hacker_id;