Hacker - submissions Interview Questions, Sql Server

Hacker - submissions Interview Questions

/*
Question 1:
You have 2 tables.
1. Hackers(hacker_id,name)
2. Submissions(hacker_id,submission_id,challenge_id,score)
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. 
*/

create table Hackers(hacker_id int,name varchar(255));
create table Submissions(hacker_id int,submission_id int,challenge_id int,score int);

insert into Hackers values(1,'El');
insert into Hackers values(2,'Shinchan');
insert into Hackers values(3,'Harry');
insert into Hackers values(4,'Pallavi');

insert into Submissions values(1,1,1,80);
insert into Submissions values(1,2,1,100);
insert into Submissions values(1,1,2,50);
insert into Submissions values(2,1,1,0);
insert into Submissions values(3,1,1,100);
insert into Submissions values(3,1,2,20);

select * from Hackers;
select * from Submissions;

select a.hacker_id,b.name,sum(a.score) as total_score
from
(
    select hacker_id,challenge_id,max(score) as score
    from Submissions
    group by hacker_id,challenge_id
)a
join
(
    select * from Hackers
)b
on a.hacker_id = b.hacker_id
group by a.hacker_id,b.name
having sum(a.score)>0
order by sum(a.score) desc,a.hacker_id


run \| edit \| history \| help	0