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.
  1. Prepare
  2. SQL
  3. Basic Join
  4. Challenges
  5. Discussions

Challenges

Sort by

recency

|

2556 Discussions

|

  • anslemdcunha
     + 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

  • rohansrinivas810
     + 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))

  • amahesh1989
     + 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;

  • frnteq
     + 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;

  • la_jiali_shen
     + 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