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

|

2606 Discussions

|

  • dotranminhkhue
     + 0 comments

    -- Joining tables to make full raw data with cte as ( select h.hacker_id, h.name, count(c.challenge_id) as total_challenge from Hackers h join Challenges c on h.hacker_id = c.hacker_id group by h.hacker_id, h.name),

    -- For each total number of challenges, count how many hackers achieved it. Exclude the ones that are duplicated and smaller than the highest. cte1 as ( select total_challenge, count(hacker_id) total_hacker from cte group by total_challenge having count(hacker_id) = 1)

    select * from cte where total_challenge = (select max(total_challenge) from cte) or total_challenge in (select total_challenge from cte1) order by total_challenge desc, hacker_id;

  • gs06sid
     + 0 comments
    WITH
cte AS(
        SELECT 
            h.hacker_id, 
            h.name, 
            COUNT(c.challenge_id) AS challenge_count
        FROM hackers h
        JOIN challenges c ON c.hacker_id = h.hacker_id
        GROUP BY h.hacker_id, h.name
        ),
cte2 AS(
        SELECT challenge_count, COUNT(hacker_id) AS hacker_count
        FROM cte
        GROUP BY challenge_count
        HAVING COUNT(hacker_id) = 1
        )
SELECT *
FROM cte
WHERE 
    challenge_count = (SELECT MAX(challenge_count) FROM cte)
    OR
    challenge_count IN (SELECT challenge_count FROM cte2)
ORDER BY challenge_count DESC, hacker_id
;``

  • hahayeahnah
     + 0 comments

    Not the prettiest but should be simple enough to follow:

    with 
    challenge_count as (
        select
            c.hacker_id id,
            h.name name,
            count(c.hacker_id) ct
        from
            challenges c,
            hackers h
        where
            c.hacker_id = h.hacker_id
        group by
            id,
            name
        order by
            ct desc
    )
select
    *
from
    challenge_count c
where
    ct = (
            select max(ct)
            from challenge_count) or
    1 = (
            select count(ct)
            from challenge_count
            where c.ct = ct
        )
order by
    ct desc,
    id asc;

  • ramoncassilha
     + 0 comments

    WITH challenge_counts AS ( select h.hacker_id, h.name, count(c.challenge_id) as challenges_created from hackers h join challenges c on h.hacker_id = c.hacker_id group by h.hacker_id, h.name ), count_frequencies AS ( SELECT cc.challenges_created, COUNT(*) AS frequency FROM challenge_counts cc GROUP BY cc.challenges_created ) SELECT cc.hacker_id, cc.name, cc.challenges_created from challenge_counts cc where cc.challenges_created = (SELECT MAX(challenges_created) from challenge_counts) or cc.challenges_created IN ( SELECT cf.challenges_created FROM count_frequencies cf WHERE cf.frequency = 1 ) order by cc.challenges_created desc, cc.hacker_id

  • prajwald1998
     + 0 comments

    with cte as ( select h.hacker_id as hid , h.name as hname, count(c.challenge_id) as aaa from hackers h join Challenges c on h.hacker_id=c.hacker_id group by h.hacker_id , h.name ), cte1 as ( select hid, hname,aaa, lead(aaa,1) over (order by aaa desc) as bbb from cte ) select hid, hname,aaa from cte1 where aaa in (select max(aaa) from cte) union select hid, hname,aaa from cte1 where not aaa in (select aaa from cte1 where aaa=bbb) order by 3 desc, 1