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.
- Prepare
- SQL
- Basic Join
- Challenges
- Discussions
Challenges
Challenges
Sort by
recency
|
2556 Discussions
|
Please Login in order to post a comment
---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))
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;
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