0

I was solving a problem on HackerRank

You did such a great job helping Julia with her last coding contest challenge that she wants you to work on this one, too!

The total score of a hacker is the sum of their maximum scores for all of the challenges. Write a query to print the hacker_id, name, and total score of the hackers ordered by the descending score. If more than one hacker achieved the same total score, then sort the result by ascending hacker_id. Exclude all hackers with a total score of from your result.

The code I have written :

With max_score as (
select max(score) as mscore, hacker_id, challenge_id
from submissions
group by hacker_id, challenge_id
)
select h.hacker_id, h.name, sum(ms.mscore)
from hackers h join max_score ms on ms.hacker_id = h.hacker_id
group by h.hacker_id, h.name
having sum(ms.mscore)>0
order by sum(ms.mscore) desc, hacker_id asc;

For this I got the below 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 'maxscore as ( select max(score) as mscore, hacker_id, challenge_id from submissi' at line 1
Your Output (stdout)
~ no response on stdout ~ BlogScoringEnvironmentFAQAbout UsSupportCareersTerms Of ServicePrivacy Policy

Please tell me what is the problem with the code and I cannot understand the error.

Also the code is properly running in MS SQL Server but causing error in MySQL

Thom A
  • 88,727
  • 11
  • 45
  • 75
Parakh
  • 1
  • 2
    MySQL and SQL Server are completely different produce and use different dialects of SQL. Just because your SQL works in one data engine doesn't mean it will in another; in fact it's rare that it will unless it's a very simple statement. – Thom A Aug 16 '23 at 10:02
  • 1
    According to [this answer](https://stackoverflow.com/a/1382618/2029983) MySQL didn't support CTEs until MySQL 8.0 (which was release in 2018). SQL Server, on the other hand, added support for them way back in SQL Server 2005 (released in the same year). Which version of MySQL are you using? – Thom A Aug 16 '23 at 10:05
  • 1
    Hackerrank uses MySQL ver. *5.7.27-0ubuntu0.18.04.1* - i.e. it does NOT support CTEs. Test your query on MS SQL Server whose version is *Microsoft SQL Server 2022 (RTM-CU3) (KB5024396) - 16.0.4025.1 (X64) Mar 13 2023 19:10:08 Copyright (C) 2022 Microsoft Corporation Express Edition (64-bit) on Linux (Ubuntu 20.04.6 LTS) * – Akina Aug 16 '23 at 10:18

1 Answers1

0

Rewrite it so that it doesn't use a CTE, but a subquery:

  SELECT h.hacker_id, h.name, SUM (ms.mscore)
    FROM hackers h
         JOIN (
                   SELECT MAX (score) AS mscore, hacker_id, challenge_id
                     FROM submissions
                 GROUP BY hacker_id, challenge_id
              ) ms
            ON ms.hacker_id = h.hacker_id
GROUP BY h.hacker_id, h.name
  HAVING SUM (ms.mscore) > 0
ORDER BY SUM (ms.mscore) DESC, hacker_id ASC;

BTW, do you need challenge_id in select (and, consequentially, in group by clause)? You don't reference it in main query, so perhaps you could simplify it to

  SELECT h.hacker_id, h.name, SUM (ms.mscore)
    FROM hackers h
         JOIN (
                   SELECT MAX (score) AS mscore, hacker_id
                     FROM submissions
                 GROUP BY hacker_id
              ) ms
            ON ms.hacker_id = h.hacker_id
GROUP BY h.hacker_id, h.name
  HAVING SUM (ms.mscore) > 0
ORDER BY SUM (ms.mscore) DESC, hacker_id ASC;
Littlefoot
  • 131,892
  • 15
  • 35
  • 57