Zahid Zahid - 3 months ago 6
SQL Question

Query for total score

Input Table: Submissions

Submission contains four columns , Contestant_id indicates id of different contestants who submit against different problems.One contestant can submit against a problem more than one time.So,challenge_id may appear more than once against a Contestant_id.

submission_id Contestant_id challenge_id score
11 1 333 90
22 2 333 60
33 3 333 80
44 4 333 0
112 1 333 45
113 1 444 80
114 2 444 70


Output Table: Total Score

Contestant_id score
1 170
2 130
3 80


Here, we take total score as-

for contestant_id 1 : total score = max(90,45)+ 80 = 170
for contestant_id 2 : total score = 60 + 70 = 130
for contestant_id 3 : total score = 80
for contestant_id 4 : total score = 0 ;so we exclude it


for taking total score, I have to take maximum of a single contestant_id,if there is more than one same challenge_id,than take a sum.But,I am stuck at it,how to do.

Select Contestant_id,Score as
(Select sum (max(Select .... )

Answer

You seem to need two levels of aggregation. You need the maximum score for each contestant and challenge. Then you want to add these up.

Here is one method:

select Contestant_id, sum(max_score)
from (select Contestant_id, challenge_id, max(score) as max_score
      from Submissions
      group by Contestant_id, challenge_id,
     ) t
group by Contestant_id;

If you want to create an output table, you can add into total_scores after the select.