Challenges

Sort by

recency

|

2556 Discussions

|

  • + 0 comments
    with cte1 as (select hacker_id, count(challenge_id) cnt
                   from Challenges
                   group by hacker_id),
        cte2 as (select cnt, count(cnt) ccnt
                from cte1
                group by cnt
                having count(cnt) = 1)
    
    select h.hacker_id , h.name , count(c.challenge_id) 
    From Challenges c
    Join Hackers h
    On c.hacker_id = h.hacker_id
    group by h.hacker_id, h.name
    having (count(c.challenge_id) in (select cnt
                                    from cte2))
            or (count(c.challenge_id) in (select max(cnt)
                                    from cte1))
                                    
    order by count(c.challenge_id) desc, h.hacker_id
    
  • + 0 comments

    ---Mysql

    with cte as( select h.hacker_id, h.name, count(c.challenge_id) as count_val, dense_rank() over (partition by h.hacker_id, h.name order by count(c.challenge_id) ) as den_rank from Hackers h join Challenges c on h.hacker_id = c.hacker_id group by h.hacker_id, h.name order by count_val desc) select hacker_id, name, count_val from cte where count_val in ((select count_val from cte group by count_val having count(count_val) = 1) union (select max(count_val) from cte))

  • + 0 comments

    with cte as( select hacker_id,name,cnt,dense_rank() over(order by cnt desc) as rnk from ( 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) ), cte_rnk_1 as(select * from cte where rnk=1), cte_rem as (select * from cte where rnk>1), union_set as( select hacker_id,name,cnt from cte_rnk_1 union all select hacker_id,name,cnt from cte_rem where rnk not in (select rnk from cte_rem group by rnk having count(*) >1) ) select * from union_set order by 3 desc,1;

  • + 0 comments
    WITH CHALL_INFO AS (
        SELECT *, COUNT(*) OVER(PARTITION BY CREATED_AMT) TTL_AMT
        FROM (
            SELECT DISTINCT HACKER_ID, COUNT(*) OVER(PARTITION BY HACKER_ID) CREATED_AMT
            FROM CHALLENGES
        ) A
    )
    SELECT A.HACKER_ID, B.NAME, A.CREATED_AMT
    FROM CHALL_INFO A
    LEFT JOIN HACKERS B ON A.HACKER_ID=B.HACKER_ID
    WHERE TTL_AMT=1 OR CREATED_AMT=(SELECT MAX(CREATED_AMT) FROM CHALL_INFO)
    ORDER BY A.CREATED_AMT DESC, A.HACKER_ID;
    
  • + 0 comments

    MySQL WITH count_cha AS( SELECT h.hacker_id ,name ,COUNT(challenge_id) AS count_of_challenges FROM Challenges c JOIN Hackers h ON c.hacker_id = h.hacker_id GROUP BY 1,2)

    SELECT hacker_id ,name ,count_of_challenges FROM count_cha WHERE count_of_challenges IN ( SELECT count_of_challenges FROM count_cha GROUP BY count_of_challenges HAVING COUNT(hacker_id)=1) OR count_of_challenges = ( SELECT MAX(count_of_challenges) FROM count_cha ) GROUP BY 1,2,3 ORDER BY count_of_challenges DESC, hacker_id