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.
with TotalChallenge as (
select distinct hacker_id,
count(challenge_id) over(partition by hacker_id) as Total_challenge
from Challenges
),
Max as (
select *,
max(Total_challenge) over() as max_chal_created,
count(Total_challenge) over(partition by total_challenge) as more_hacker
from TotalChallenge
where Total_challenge > 1
),
flag AS (
SELECT *,
CASE
WHEN more_hacker > 1 AND Total_challenge <> max_chal_created THEN 0
ELSE 1
END AS Flags
FROM Max
)
SELECT flag.hacker_id, name, Total_challenge
FROM flag
JOIN Hackers h on h.hacker_id = flag.hacker_id
WHERE flags = 1
ORDER BY Total_challenge DESC ,flag.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 →
SQL SERVER