We use cookies to ensure you have the best browsing experience on our website. Please read our cookie policy for more information about how we use cookies.
-- Joining tables to make full raw data
with cte as (
select
h.hacker_id,
h.name,
count(c.challenge_id) as total_challenge
from Hackers h
join Challenges c
on h.hacker_id = c.hacker_id
group by h.hacker_id, h.name),
-- For each total number of challenges, count how many hackers achieved it. Exclude the ones that are duplicated and smaller than the highest.
cte1 as (
select
total_challenge,
count(hacker_id) total_hacker
from cte
group by total_challenge
having count(hacker_id) = 1)
select * from cte
where total_challenge = (select max(total_challenge) from cte)
or total_challenge in (select total_challenge from cte1)
order by total_challenge desc, hacker_id;
Cookie support is required to access HackerRank
Seems like cookies are disabled on this browser, please enable them to open this website
Challenges
You are viewing a single comment's thread. Return to all comments →
-- Joining tables to make full raw data with cte as ( select h.hacker_id, h.name, count(c.challenge_id) as total_challenge from Hackers h join Challenges c on h.hacker_id = c.hacker_id group by h.hacker_id, h.name),
-- For each total number of challenges, count how many hackers achieved it. Exclude the ones that are duplicated and smaller than the highest. cte1 as ( select total_challenge, count(hacker_id) total_hacker from cte group by total_challenge having count(hacker_id) = 1)
select * from cte where total_challenge = (select max(total_challenge) from cte) or total_challenge in (select total_challenge from cte1) order by total_challenge desc, hacker_id;