SQL Interview — 4

HackerRank Question

Challenges | HackerRank

declare @Hackers Table (Hacker_Id int, Name varchar(20))
insert into @Hackers Values (5077, 'Rose'), (21283,'Angela'),(62743,'Frank'),(88255,'Patrick'),(96196,'Lisa')
--select * from @Hackers
declare @Challenges Table (Challenge_ID int, Hacker_Id int)
insert into @Challenges Values (61654,5077),(58302,21283),(40587,88255),(29477,5077),(1220,21283),(69514,21283),(46561,62743),
(58077,62743),(18483,88255),(76766,21283),(52382,5077),(74467,21283),(33625,96196),(26053,88255),(42665,62743),(12859,62743),(70094,21283),
(34599,88255),(54680,88255),(61881,5077)
--select * from @Challenges

select C.Hacker_Id, H.Name, count(Challenge_ID)
Get Learning Zone’s stories in your inbox
Join Medium for free to get updates from this writer.
Remember me for faster sign in
from @Hackers H Inner Join @Challenges C
ON H.Hacker_Id = C.Hacker_Id
group by C.Hacker_Id, H.Name
having count(Challenge_ID) not in (select x.ChallengeCount from (
select C.Hacker_Id, H.Name, count(Challenge_ID) as ChallengeCount
from @Hackers H Inner Join @Challenges C
ON H.Hacker_Id = C.Hacker_Id
group by C.Hacker_Id, H.Name
) x
where x.ChallengeCount <> (select top 1 count(Challenge_ID) from @Challenges group by hacker_Id order by 1 desc)
group by x.ChallengeCount
having count(x.challengecount) > 1)
order by 3 desc, 1