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

|

2633 Discussions

|

  • yy90008889
     + 0 comments

    with cc as( select h.hacker_id, h.name, count(c.challenge_id) as total from Hackers h JOIN Challenges c ON h.hacker_id = c.hacker_id group by h.hacker_id, h.name ), scored as ( select hacker_id, name, total, count(*) over(PARTITION BY total) as freq_total, max(total) over () as max_total from cc ) select hacker_id, name, total from scored where total = max_total OR freq_total = 1 order by total desc, hacker_id;

  • hatrangqluu
     + 0 comments

    SELECT t.list_hacker, name.name, t.Total_challenge FROM ( SELECT hacker_id AS list_hacker, COUNT(challenge_id) AS Total_challenge, COUNT(*) OVER (PARTITION BY COUNT(challenge_id)) AS challenge_cnt FROM Challenges GROUP BY hacker_id ) AS t LEFT JOIN Hackers name ON t.list_hacker = name.hacker_id WHERE challenge_cnt = 1 OR Total_challenge = ( SELECT MAX(cnt) FROM ( SELECT COUNT(challenge_id) AS cnt FROM Challenges GROUP BY hacker_id ) AS sub ) ORDER BY t.Total_challenge DESC, t.list_hacker ;

  • 20psp20_cd
     + 0 comments

    with final_cte as ( select hacker_id, name, ch_created, CASE WHEN ch_created=max(ch_created) over() then 'Yes' ELSE 'No' End as flag, count(ch_created) over(partition by ch_created) as duplicates from ( select h.hacker_id, h.name, count(distinct c.challenge_id) as ch_created from Hackers h left join Challenges c on c.hacker_id=h.hacker_id group by 1,2 ) as t )

    select hacker_id, name, ch_created from final_cte where (flag='Yes' or duplicates=1) order by 3 desc , 1

  • hanzalahali1062
     + 0 comments
    with cte as(
select hackers.hacker_id as id,name as name,count(challenges.challenge_id) as noOfChallenges from hackers
inner join challenges
on hackers.hacker_id=challenges.hacker_id
group by hackers.hacker_id,name

)

select cte.id,cte.name,cte.noOfChallenges from cte
where cte.noOfChallenges in(
select cte.noOfChallenges from cte
group by cte.noOfChallenges
having count(cte.noOfChallenges)=1 or cte.noOfChallenges=(select max(cte.noOfChallenges) from cte)
)
order by cte.noOfChallenges desc, cte.id asc;

  • mrun_palkar
     + 0 comments

    WITH test AS (SELECT h.hacker_id, name, count(challenge_id) as 'challenge_created' FROM Hackers h JOIN challenges c ON h.hacker_id = c.hacker_id GROUP by h.hacker_id, name Order BY count(challenge_id) DESC, h.hacker_id)

    SELECT hacker_id, name, challenge_created FROM test WHERE challenge_created = (SELECT MAX(challenge_created) FROM test)

    UNION

    SELECT hacker_id, name, t2.challenge_created FROM

    (SELECT challenge_created FROM test GROUP BY challenge_created Having count(challenge_created)=1) t2

    JOIN

    test

    ON t2.challenge_created = test.challenge_created