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.
mysql
-- define max_challenges to exclude from refuse_criteria
with max_challenges as (
select max(total_challenges) max_challenge_count from (
select hacker_id, count(challenge_id) total_challenges from challenges group by 1)t
)
-- turns challenge_count into a dimension to create the refuse_criteria (excludes the max_one), can simplify this by using row_number and excluding the 1st row as well
, refuse_criteria as (
select
challenge_count dim_challenge_count,
count(hacker_id) hackers
from (
select
hacker_id,
count(challenge_id) challenge_count
from challenges group by 1
) t
where challenge_count < (select max_challenge_count from max_challenges)
group by 1
having count(hacker_id) > 1
)
-- given refuse_criteria, now we do something similar, but filter in only refused_hacker_ids
, refused_hacker_ids as (
select
hacker_id
from (
select
hacker_id,
count(challenge_id) challenge_count
from challenges group by 1
) t
inner join refuse_criteria rc on rc.dim_challenge_count = t.challenge_count
)
-- final query with refused_hacker_ids.hacker_id is null
select
h.hacker_id, h.name, count(c.challenge_id) challenges_created
from hackers h
left join refused_hacker_ids r on r.hacker_id = h.hacker_id
inner join challenges c on c.hacker_id = h.hacker_id
where r.hacker_id is null
group by 1,2 order by 3 desc, 1 asc
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 →
mysql -- define max_challenges to exclude from refuse_criteria with max_challenges as ( select max(total_challenges) max_challenge_count from ( select hacker_id, count(challenge_id) total_challenges from challenges group by 1)t )
-- turns challenge_count into a dimension to create the refuse_criteria (excludes the max_one), can simplify this by using row_number and excluding the 1st row as well , refuse_criteria as ( select challenge_count dim_challenge_count, count(hacker_id) hackers from ( select hacker_id, count(challenge_id) challenge_count from challenges group by 1 ) t where challenge_count < (select max_challenge_count from max_challenges) group by 1 having count(hacker_id) > 1 )
-- given refuse_criteria, now we do something similar, but filter in only refused_hacker_ids , refused_hacker_ids as ( select hacker_id from ( select hacker_id, count(challenge_id) challenge_count from challenges group by 1 ) t inner join refuse_criteria rc on rc.dim_challenge_count = t.challenge_count )
-- final query with refused_hacker_ids.hacker_id is null select h.hacker_id, h.name, count(c.challenge_id) challenges_created from hackers h left join refused_hacker_ids r on r.hacker_id = h.hacker_id inner join challenges c on c.hacker_id = h.hacker_id where r.hacker_id is null group by 1,2 order by 3 desc, 1 asc