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

  • jaredjurss
     + 0 comments

    Working in Oracle and this took me a long time to get working. Not sure how efficient this is or if its overly complex

    select hacker_id, name, total_challenges from 
(select c.hacker_id, h.name, count(c.hacker_id) as total_challenges, count(count(c.hacker_id)) over(partition by count(c.hacker_id)) as dup, max(count(c.hacker_id)) over() as max_challenges
from challenges c join hackers h on c.hacker_id = h.hacker_id
group by c.hacker_id, h.name)
where total_challenges = max_challenges or dup = 1
order by total_challenges desc, hacker_id;