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.
table 1: Hackers
The hacker_id is the id of the hacker, and name
is the name of the hacker.
table 2: Submissions
The
submission_id is the id of the submission, hacker_id is the id of the hacker
who made the submission, challenge_id is the id of the challenge for which the
submission belongs to, and score is the score of the submission.
Solutions:
with cte as
(
select max(s.score) as max_scores,h.hacker_id,h.name from Hackers as h
join Submissions as s on h.hacker_id = s.hacker_id
group by s.challenge_id,h.hacker_id,h.name
)
select hacker_id,name,sum(max_scores) as total_scores from cte
group by hacker_id,name
having sum(max_scores) > 0
order by sum(max_scores) desc,hacker_id
Output:
SQL Script:
USE [HackerRank]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Hackers](
[hacker_id]
[int] NOT NULL,
[name]
[nvarchar](50) NULL,
CONSTRAINT [PK_Hackers] PRIMARY KEY CLUSTERED
(
[hacker_id]
ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Submissions](
[submission_id]
[int] NOT NULL,
[hacker_id]
[int] NULL,
[challenge_id]
[int] NULL,
[score]
[int] NULL,
CONSTRAINT [PK_Submission1] PRIMARY KEY CLUSTERED
(
[submission_id]
ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
INSERT [dbo].[Hackers] ([hacker_id], [name]) VALUES (4071, N'Rose')
GO
INSERT [dbo].[Hackers] ([hacker_id], [name]) VALUES (4806, N'Angela')
GO
INSERT [dbo].[Hackers] ([hacker_id], [name]) VALUES (26071, N'Frank')
GO
INSERT [dbo].[Hackers] ([hacker_id], [name]) VALUES (49438, N'Patrick')
GO
INSERT [dbo].[Hackers] ([hacker_id], [name]) VALUES (74842, N'Lisa')
GO
INSERT [dbo].[Hackers] ([hacker_id], [name]) VALUES (80305, N'Kimberly')
GO
INSERT [dbo].[Hackers] ([hacker_id], [name]) VALUES (84072, N'Bonnie')
GO
INSERT [dbo].[Hackers] ([hacker_id], [name]) VALUES (87868, N'Michael')
GO
INSERT [dbo].[Hackers] ([hacker_id], [name]) VALUES (92118, N'Todd')
GO
INSERT [dbo].[Hackers] ([hacker_id], [name]) VALUES (95895, N'Joe')
GO
INSERT [dbo].[Submissions] ([submission_id], [hacker_id], [challenge_id], [score]) VALUES (4002, 26071, 89343, 36)
GO
INSERT [dbo].[Submissions] ([submission_id], [hacker_id], [challenge_id], [score]) VALUES (6943, 4071, 19797, 95)
GO
INSERT [dbo].[Submissions] ([submission_id], [hacker_id], [challenge_id], [score]) VALUES (9951, 4071, 49593, 43)
GO
INSERT [dbo].[Submissions] ([submission_id], [hacker_id], [challenge_id], [score]) VALUES (10063, 4071, 49593, 96)
GO
INSERT [dbo].[Submissions] ([submission_id], [hacker_id], [challenge_id], [score]) VALUES (12855, 4806, 25917, 13)
GO
INSERT [dbo].[Submissions] ([submission_id], [hacker_id], [challenge_id], [score]) VALUES (14115, 4806, 49593, 76)
GO
INSERT [dbo].[Submissions] ([submission_id], [hacker_id], [challenge_id], [score]) VALUES (17513, 4806, 49593, 32)
GO
INSERT [dbo].[Submissions] ([submission_id], [hacker_id], [challenge_id], [score]) VALUES (23363, 26071, 19797, 29)
GO
INSERT [dbo].[Submissions] ([submission_id], [hacker_id], [challenge_id], [score]) VALUES (31093, 26071, 19797, 2)
GO
INSERT [dbo].[Submissions] ([submission_id], [hacker_id], [challenge_id], [score]) VALUES (44829, 26071, 89343, 17)
GO
INSERT [dbo].[Submissions] ([submission_id], [hacker_id], [challenge_id], [score]) VALUES (45104, 49438, 25917, 34)
GO
INSERT [dbo].[Submissions] ([submission_id], [hacker_id], [challenge_id], [score]) VALUES (49742, 26071, 49593, 20)
GO
INSERT [dbo].[Submissions] ([submission_id], [hacker_id], [challenge_id], [score]) VALUES (52826, 49438, 49593, 9)
GO
INSERT [dbo].[Submissions] ([submission_id], [hacker_id], [challenge_id], [score]) VALUES (53795, 74842, 19797, 5)
GO
INSERT [dbo].[Submissions] ([submission_id], [hacker_id], [challenge_id], [score]) VALUES (64479, 74842, 19797, 98)
GO
INSERT [dbo].[Submissions] ([submission_id], [hacker_id], [challenge_id], [score]) VALUES (67194, 74842, 63132, 76)
GO
INSERT [dbo].[Submissions] ([submission_id], [hacker_id], [challenge_id], [score]) VALUES (69846, 80305, 19797, 19)
GO
INSERT [dbo].[Submissions] ([submission_id], [hacker_id], [challenge_id], [score]) VALUES (73343, 80305, 49593, 42)
GO
INSERT [dbo].[Submissions] ([submission_id], [hacker_id], [challenge_id], [score]) VALUES (75147, 80305, 49593, 48)
GO
INSERT [dbo].[Submissions] ([submission_id], [hacker_id], [challenge_id], [score]) VALUES (81614, 84072, 49593, 100)
GO
INSERT [dbo].[Submissions] ([submission_id], [hacker_id], [challenge_id], [score]) VALUES (84264, 84072, 63132, 0)
GO