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
|
2320 Discussions
|
Please Login in order to post a comment
SELECT h.hacker_id, h.name, COUNT(c.challenge_id) AS total_challenges FROM Hackers h JOIN Challenges c ON h.hacker_id = c.hacker_id GROUP BY h.hacker_id, h.name HAVING total_challenges = (SELECT COUNT(challenge_id) FROM Challenges GROUP BY hacker_id ORDER BY COUNT(challenge_id) DESC LIMIT 1) ORDER BY total_challenges DESC, h.hacker_id;
MySQL
with cte as (select h.hacker_id,h.name,count(c.challenge_id) total from hackers h join challenges c on h.hacker_id=c.hacker_id group by h.hacker_id,h.name),
cte2 as (select distinct h.hacker_id, h.name, h.total from cte as h, cte as b
where h.hacker_id <> b.hacker_id and h.total=b.total and h.total < (select max(total) as max_total from cte))
select h.hacker_id,h.name, h.total from cte h where h.hacker_id not in (select hacker_id from cte2) order by h.total desc ,h.hacker_id