Run Code
|
API
|
Code Wall
|
Misc
|
Feedback
|
Login
|
Theme
|
Privacy
|
Patreon
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
QLDT TUAN 6
SQL Server - UK Holiday Calendar Example
Pivot and Unpivot
Alquileres
VANYA
Exam_1
20181CSE0041
QLSV
Use the right tool to get identity values back after an insert
My notes