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

|

2649 Discussions

|

  • eneeyan_nanmaran
     + 0 comments

    with result as(select a.hacker_id,name,count() as ct from challenges as a inner join hackers as b on a.hacker_id = b.hacker_id group by 1,2 order by count() desc,a.hacker_id asc) select * from result where ct not in (select ct from result group by ct having count(*)>1)or ct = (select max(ct) from result );

  • jarun_vtn
     + 0 comments
    with cte as (select h.hacker_id, h.name, count(challenge_id) as n_challenge
from hackers as h
left join challenges as c on h.hacker_id = c.hacker_id
group by h.hacker_id, h.name
order by n_challenge desc)

select *
from cte
where n_challenge = (select max(n_challenge) from cte) or n_challenge in (
    select n_challenge
    from cte
    group by n_challenge
    having count(*) = 1
    )
;

  • datasciencemast1
     + 0 comments

    WITH T1 AS(SELECT C.Hacker_Id HID, COUNT(Challenge_Id) CNT FROM Challenges C GROUP BY C.Hacker_Id), T2 AS(SELECT HID, CNT, Count(CNT) OVER(PARTITION BY CNT) AGG FROM T1), T3 AS(SELECT HID, CNT, AGG, MAX(CNT) OVER() MC FROM T2), T4 AS (SELECT HID, H.NAME NAME, CNT, AGG FROM T3 LEFT JOIN Hackers H ON H.Hacker_Id= HID WHERE CNT=50 OR AGG=1) SELECT HID, NAME, CNT FROM T4 ORDER BY CNT DESC, HID;

  • parthee31591
     + 0 comments

    SELECT final.hacker_id, final.name, aa from ( select ha.hacker_id, ha.name, count(chal.challenge_id) as aa, count(ha.name) over(partition by count(chal.challenge_id) order by count(chal.challenge_id)) as row1 from hackers as ha JOIN challenges as chal on chal.hacker_id = ha.hacker_id group by ha.hacker_id, ha.name

    ) as Final where row1 <= 1 or aa = 50 order by aa desc, final.hacker_id

  • tarasovvolodya
     + 0 comments

    WITH t1 AS ( SELECT hacker_id, COUNT(challenge_id) AS qty FROM CHALLENGES GROUP BY hacker_id HAVING COUNT(challenge_id) <> (SELECT TOP 1 COUNT(challenge_id)
    FROM CHALLENGES GROUP BY hacker_id ORDER BY COUNT(challenge_id) DESC)), t2 AS(
    SELECT qty, COUNT(qty) AS freq FROM t1 GROUP BY t1.qty HAVING COUNT(qty) <> 1 )

    SELECT h.hacker_id, h.name, COUNT(c.challenge_id) AS q FROM HACKERS h LEFT JOIN CHALLENGES c ON c.hacker_id = h.hacker_id GROUP BY h.hacker_id, h.name HAVING COUNT(c.challenge_id) NOT IN (SELECT qty FROM t2) AND COUNT(c.challenge_id) <> 0 ORDER BY COUNT(c.challenge_id) DESC, h.hacker_id