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

|

2437 Discussions

|

  • pranshupatel934
     + 0 comments

    SELECT c.hacker_id, h.name, count(c.challenge_id) AS cnt FROM Hackers AS h JOIN Challenges AS c ON h.hacker_id = c.hacker_id GROUP BY c.hacker_id, h.name HAVING cnt = (SELECT count(c1.challenge_id) FROM Challenges AS c1 GROUP BY c1.hacker_id ORDER BY count(*) desc limit 1) or cnt NOT IN (SELECT count(c2.challenge_id) FROM Challenges AS c2 GROUP BY c2.hacker_id HAVING c2.hacker_id <> c.hacker_id) ORDER BY cnt DESC, c.hacker_id;

  • raghunand_gokul
     + 0 comments
    with challenges_count as (
    select c.hacker_id as id, h.name as name, count(*) as c_count
    from hackers h  join challenges c on h.hacker_id = c.hacker_id 
    group by c.hacker_id, h.name
)

select id, name, c_count 
from challenges_count 
where c_count 
in (select c_count
    from challenges_count
    group by c_count 
    having count(c_count) = 1 or c_count = (select max(c_count) from challenges_count) ) 
order by c_count desc, id;

  • jainkunalmanish
     + 0 comments

    with cte as ( select h.hacker_id,h.name,count(c.challenge_id) as no_of_challenges from hackers h join challenges c on h.hacker_id = c.hacker_id group by h.hacker_id,h.name order by count(c.challenge_id) desc,h.hacker_id asc ) select hacker_id,name,no_of_challenges from cte where no_of_challenges in (select no_of_challenges from cte group by no_of_challenges having (no_of_challenges>=50) or (count(*)<2 and no_of_challenges<50)); `

  • amit16aks
     + 0 comments

    with cte1 as (select h.hacker_id as hacker_id, name, count(challenge_id) as challenge_created from hackers h join challenges c on h.hacker_id = c.hacker_id group by h.hacker_id, name order by challenge_created desc, hacker_id),

    cte2 as ( select challenge_created, count(hacker_id) no_of_students from cte1 group by challenge_created)

    select hacker_id, name, cte1.challenge_created #,no_of_students from cte1 join cte2 on cte1.challenge_created = cte2.challenge_created where cte1.challenge_created = (select max(challenge_created) from cte1) or no_of_students < 2;

  • madhanpvs_ee
     + 0 comments

    WITH CTE1 AS ( SELECT HACKER_ID, COUNT(CHALLENGE_ID) AS CNT1 FROM CHALLENGES GROUP BY HACKER_ID ), CTE2 AS ( SELECT CNT1, COUNT(CNT1) AS CNT2 FROM CTE1 GROUP BY CNT1 HAVING CNT1 = (SELECT MAX(CNT1) FROM CTE1) OR COUNT(CNT1) = 1 ) SELECT C1.HACKER_ID,HC.NAME, C1.CNT1 FROM CTE1 C1 INNER JOIN CTE2 C2 ON C1.CNT1 = C2.CNT1 INNER JOIN HACKERS HC ON HC.HACKER_ID = C1.HACKER_ID ORDER BY C1.CNT1 DESC, C1.HACKER_ID ASC