Discussion on Challenges Challenge

Sort by

recency

|

2538 Discussions

|

21 hours ago+ 1 comment

select 
    h.hacker_id,
    h.name,
    l2.chals_done
from Hackers h join (
    select 
        hacker_id,
        chals_done, 
        rankings,
        count(rankings) over (partition by chals_done) as filter
    from (
        select 
            hacker_id,
            count(challenge_id) as chals_done,
            dense_rank() over (order by count(challenge_id) desc) as rankings
        from Challenges
        group by hacker_id
        ) l1
    group by hacker_id, chals_done, rankings
    order by chals_done desc, rankings asc, hacker_id asc
    ) l2 on h.hacker_id = l2.hacker_id
where (l2.rankings = 1) or (l2.filter = 1)
order by l2.chals_done desc, h.hacker_id asc

20 hours ago+ 0 comments

fun challenge! I created two derived tables, the first one called L1 where I count the number of challenges created by each hacker and the dense rank -important not to use simply rank- of each one, and I use this as a subquery in the FROM clause to create L2 where I count how many times each rank appears (that is, how many times a number of challenges created by a hacker is repeated) in L1. afterwards, I just exclude every record with a repeated number of challenges created by an individual (l2.filter = 1) with the exception of the highest possible number of ranks (l2.rankings = 1)

1 day ago+ 0 comments

MYSQL:

WITH CNTTABLE AS (SELECT H.hacker_id, H.name, count(C.challenge_id) CNT    
FROM Hackers H, Challenges C
WHERE H.hacker_id = C.hacker_id
GROUP BY H.hacker_id, H.name
ORDER BY CNT DESC, H.hacker_id)

SELECT CNTTABLE.hacker_id, CNTTABLE.name, CNTTABLE.CNT FROM CNTTABLE
JOIN (SELECT CNT, COUNT(distinct hacker_id) UnqHacker FROM CNTTABLE
      GROUP BY CNT
      HAVING CNT = (SELECT MAX(CNT) FROM CNTTABLE) OR UnqHacker = 1
      ORDER BY CNT DESC) SUB ON CNTTABLE.CNT = SUB.CNT

1 day ago+ 0 comments

MS SQL code

with cc as ( select h.hacker_id,h.name,count(ch.challenge_id) as cnt from hackers h inner join challenges ch on h.hacker_id=ch.hacker_id group by h.hacker_id,h.name) ,cou as (select cnt from cc group by cnt having count(cnt)>1) ,notuniq as (select * from cc where cc.cnt =(select cnt from cou where cou.cnt=(Select max(cnt) from cc))) ,final as (select * from cc where cc.cnt not in (select cnt from cou) union select * from notuniq ) select * from final order by cnt desc,hacker_id

4 days ago+ 1 comment

WITH CHALLENGE AS ( select h.hacker_id,name, count(challenge_id) as n_challenges from hackers h join challenges c on h.hacker_id = c.hacker_id group by 1,2 order by count(challenge_id) DESC ), less_max as( select hacker_id, name, n_challenges from challenge WHERE n_challenges < ( select max(n_challenges) from challenge ) ) select * from challenge where n_challenges = ( select max(n_challenges) from challenge ) union all select * from less_max where n_challenges NOT IN ( SELECT n_challenges FROM less_max GROUP BY n_challenges HAVING count(*) > 1 ) order by n_challenges desc, hacker_id asc

I solved with this approach took me 20 mins to solve

2 days ago+ 0 comments

Thanks for saying how long it took you, I was wondering how hard other people felt this was. It probably took me about the same amount of time. My solution was a little different from yours, but got to the same result.

4 days ago+ 0 comments

WITH CTE AS( SELECT C.hacker_id, name, COUNT(challenge_id) AS Total_Challenges FROM Challenges AS C JOIN Hackers AS H ON C.hacker_id = H.hacker_id GROUP BY C.hacker_id,name ), CTE1 AS( SELECT Total_Challenges,COUNT() AS Cnt FROM CTE WHERE Total_Challenges <> (SELECT MAX(Total_Challenges) FROM CTE) GROUP BY Total_Challenges HAVING COUNT()>1 ) SELECT * FROM CTE WHERE Total_Challenges NOT IN (SELECT DISTINCT(Total_Challenges) FROM CTE1) ORDER BY Total_Challenges DESC,hacker_id ASC

Sort by

|

2538 Discussions

|

Cookie support is required to access HackerRank

Please signup or login in order to view this challenge