CODEWITHSUNDEEP
mysql
Priya
Priya

Reputation: 5

Wrong output in MYSQL

I have a question as follows:

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 0 from your result.
The 2 tables are given as follows:

table: Hackers

========================================
hacker_id: Integer (ID of the hacker)
name: String (Name of the hacker)
========================================

table: Submissions

===================================================
submission_id: Integer (ID of the submission)
hacker_id:     Integer (ID of the hacker)
challenge_id:  Integer (ID of the challenge)
score:         Integer (score of the submission)
===================================================

The MYSQL query I've written is as follows:-

select 
    a.hacker_id,
    a.name,
    a.total 
from(
    select 
        h.hacker_id,
        h.name,
        sum(case when s.hacker_id=h.hacker_id then s.score else 0 end) as total 
    from 
        hackers h,
        submissions s 
    group by 
        h.hacker_id,
        h.name
) as a 
where 
    a.total>0 
order by 
    a.total desc,
    a.hacker_id asc;

I'm getting wrong output for this though that output satisfies all the rules of order & ommission of 0 scorers as required. I'm very confused as to what the error is. Someone please help!!!

Upvotes: 0

Views: 205

Answers (2)

Andreas
Andreas

Reputation: 159086

The total score of a hacker is the sum of their maximum scores for all of the challenges.

First, you need to find the maximum score a hacker, for each challenge:

SELECT hacker_id
     , challenge_id
     , MAX(score) AS max_score
  FROM Submissions
 GROUP BY hacker_id
        , challenge_id

Then you need to sum that for each hacker:

SELECT hacker_id
     , SUM(max_score) AS total_score
  FROM ( SELECT hacker_id
              , challenge_id
              , MAX(score) AS max_score
           FROM Submissions
          GROUP BY hacker_id
                 , challenge_id
       ) ms
 GROUP BY hacker_id

Finally you apply the rest:

Exclude all hackers with a total score of 0 from your result.

Print the hacker_id, name, and total score

Ordered by the descending score, then by ascending hacker_id.

SELECT hacker_id
     , ( SELECT name
           FROM Hackers h
          WHERE h.hacker_id = ms.hacker_id
       ) AS name
     , SUM(max_score) AS total_score
  FROM ( SELECT hacker_id
              , challenge_id
              , MAX(score) AS max_score
           FROM Submissions
          GROUP BY hacker_id
                 , challenge_id
       ) ms
 GROUP BY hacker_id
HAVING SUM(max_score) <> 0
 ORDER BY total_score DESC
        , hacker_id

Upvotes: 0

Lorna Mitchell
Lorna Mitchell

Reputation: 1986

Something like:

select h.hacker_id, h.name, sum(s.score) as total
from hackers h
join submissions s using (hacker_id)
group by h.hacker_id
order by sum(s.score) desc, h.name
having sum(s.score) > 0

The basic idea is that to start with we join hackers and submissions tables together, and then sum the score and use the group by clause to get a new total for each hacker. Then add the order by. Then finally the HAVING is what filters out anyone with a zero score.

I don't have your data set to test with but hopefully this is close enough that it will help you along the way!

Upvotes: 1

Related Questions