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
|
2141 Discussions
|
Please Login in order to post a comment
MySQL:
MySQL
Using Join and Where
select hid, n, t3.ccid from ( -- derived table 3 with hacker_id, name and count of challenge_id by hacker_id select hacker_id as hid, min(name) as n, count(challenge_id) as ccid from challenges as c left join hackers using (hacker_id) group by hacker_id order by ccid desc, hacker_id ) as t3 left join ( -- derived table 2 with (count of challenge_id by hacker_id) and count of (count of challenge_id by hacker_id) select min(ccid) as ccid, count(ccid) as cccid from ( -- derived table 1 with count of challenge_id by hacker_id select count(challenge_id) as ccid from challenges as c group by hacker_id ) as t1 group by ccid) as t2 using (ccid) where t2.cccid = 1 or t3.ccid = ( -- derive max of count of challenge_id by hacker_id select count(challenge_id) as ccid from challenges as c group by hacker_id order by ccid desc limit 1 )
Using a dummy frequency column
with temp as (select h.hacker_id hid,h.name hname, count(c.challenge_id) cnt, count(*) over(PARTITION BY count(c.challenge_id)) as freq from hackers h join challenges c on h.hacker_id = c.hacker_id group by h.hacker_id, h.name) select hid, hname, cnt from temp where freq = 1 or cnt in (select max(temp.cnt) from temp) order by cnt desc,hid;