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

|

2647 Discussions

|

  • 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

  • alex_dremer93
     + 0 comments
    with stats as(
select h.hacker_id,
    h.name,
    count(c.challenge_id) as total_number_of_c
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 total_number_of_c DESC
),
max_value as(
    select max(total_number_of_c) as max_tasks from stats
),
dup as(
    select total_number_of_c 
    from stats 
    group by total_number_of_c
    having count(*) > 1
)
select hacker_id, name, total_number_of_c
from stats 
where total_number_of_c not in (select total_number_of_c from dup) or 
total_number_of_c  = (select max_tasks from max_value)

  • otienophabian200
     + 0 comments

    SELECT h.hacker_id, h.name, COUNT(c.challenge_id) AS Num_Challenges FROM Hackers h INNER JOIN Challenges c ON h.hacker_id = c.hacker_id GROUP BY h.hacker_id,h.name HAVING Num_Challenges = ( SELECT MAX(challenge_count) AS max_chal FROM( SELECT COUNT(challenge_id) AS challenge_count FROM Challenges GROUP BY hacker_id) AS max_challenge) OR Num_Challenges IN ( SELECT challenge_count_a FROM ( SELECT COUNT(challenge_id) AS challenge_count_a FROM challenges GROUP BY hacker_id ) AS challenge_counts GROUP BY challenge_count_a HAVING COUNT(*) = 1)
    ORDER BY Num_Challenges DESC, h.hacker_id ASC;