I'm trying to solve this challenge by using Subquery, I don't know what are the syntax wrong in my code.

select h.hacker_id, h.name from hackers

        join (
                select s.submission_id, s.hacker_id, s.score, d.score
                        from submissions s
                        join challenges c on c.challenge_id = s.challenge_id and
                                                c.hacker_id = s.hacker_id
                        join diffculty d on c.difficulty_level = d.difficulty_level
                        where (s.score = d.score) 
                        group by s.submission_id

        ) as Result(SubId, HID, D1, D2)

        on h.hacker_id = Result.HID
        having count(Result.SubId) > 1
        order by count(Result.SubId) desc, h.name;


Error:

ERROR 1064 (42000) at line 1: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '(SubId, HID, D1, D2)

        on h.hacker_id = Result.HID
        having count(R' at line 12

this is the link of challenge: https://www.hackerrank.com/challenges/full-score/problem

1 Answers

0
The Impaler On Best Solutions

You can to (re)name the columns in the table expression itself as in:

select h.hacker_id, h.name 
from hackers

    join ( -- here you started a "table expression"
            select s.submission_id as SubId, -- renamed here 
                   s.hacker_id as HID,       -- renamed here
                   s.score as D1,            -- renamed here
                   d.score as D2             -- renamed here
            from submissions s
            join challenges c on c.challenge_id = s.challenge_id and
                                 c.hacker_id = s.hacker_id
            join diffculty d on c.difficulty_level = d.difficulty_level
            where (s.score = d.score) 
            group by s.submission_id

    ) as Result on h.hacker_id = Result.HID
group by h.hacker_id -- you need to group if you want to use HAVING
having count(Result.SubId) > 1
order by count(Result.SubId) desc, h.name;