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

|

2620 Discussions

|

  • shaswathkumar
     + 0 comments
    WITH cte1 AS(
    SELECT h.hacker_id, 
    h.name, 
    COUNT(c.challenge_id) challenges_created,
    COUNT(COUNT(c.challenge_id)) OVER(PARTITION BY COUNT(c.challenge_id)) AS duplicates
    FROM hackers h LEFT JOIN challenges c ON h.hacker_id = c.hacker_id
    GROUP BY h.hacker_id, h.name
)
SELECT hacker_id, name,challenges_created
FROM cte1
WHERE duplicates < 2 OR challenges_created = (SELECT MAX(challenges_created) FROM cte1)
ORDER BY challenges_created DESC, hacker_id;

  • jd_caicedo1008
     + 0 comments

    The main idea was first to do CTE with challenges by hackers and add a column 'duplicates' using a window function to count how many hacker_ids have the same challenge count.

    With this CTE, the next step was easy beucase I just filterd by those who doesn't have duplicates, but if they do, only return those that have the max(challenge) count. 

    WITH challenges_by_hacker AS (
    SELECT Hackers.hacker_id AS hacker_id, 
    name, 
    COUNT(challenge_id) AS num_challenges, 
    COUNT(COUNT(challenge_id)) OVER (PARTITION BY COUNT(challenge_id)) AS duplicates 
    FROM Hackers
    JOIN 
    Challenges ON Hackers.hacker_id = Challenges.hacker_id
    GROUP BY Hackers.hacker_id, name
    ORDER BY num_challenges DESC, Hackers.hacker_id
)
SELECT hacker_id, name, num_challenges
FROM challenges_by_hacker
WHERE duplicates = 1 
    OR 
    num_challenges = (
        SELECT MAX(num_challenges) FROM challenges_by_hacker
    );

  • nicelandgrow
     + 0 comments

    MySql :

    -- the first Cte Returns all hackers including those who made duplicated challenges and less than the maximum number of challenges

    WITH all_hackers AS(SELECT c.hacker_id,h.name,COUNT(*) AS total_challenges FROM Challenges c JOIN Hackers h ON c.hacker_id=h.hacker_id GROUP BY c.hacker_id,h.name ORDER BY total_challenges DESC),

    -- this Cte returns duplicated total challenges that less than the maximum number of challenges

    duplicated_challenges AS(SELECT total_challenges FROM all_hackers WHERE total_challenges < (SELECT MAX(total_challenges) FROM all_hackers) GROUP BY total_challenges HAVING COUNT(*) >1 )

    --- the final query returns hackers without those who made the same number of challenges but less than Max SELECT * FROM all_hackers WHERE total_challenges NOT IN(SELECT total_challenges FROM duplicated_challenges) ORDER BY total_challenges DESC,hacker_id

  • shubhamnarnoli0
     + 0 comments

    SELECT p.hacker_id, p.name, p.chlcnt FROM (SELECT hc.hacker_id, hc.name, CASE WHEN hckcnt.hacker_cnt>1 AND ch.challenge_count<50 THEN NULL ELSE ch.challenge_count END AS chlcnt FROM Hackers hc JOIN (SELECT hacker_id, COUNT(challenge_id) AS challenge_count FROM Challenges GROUP BY hacker_id) ch ON ch.hacker_id=hc.hacker_id LEFT JOIN (SELECT t.challenge_count,COUNT(t.hacker_id) AS hacker_cnt FROM (SELECT hc.hacker_id, hc.name, ch.challenge_count FROM Hackers hc JOIN (SELECT hacker_id, COUNT(challenge_id) AS challenge_count FROM Challenges GROUP BY hacker_id) ch ON ch.hacker_id=hc.hacker_id) t GROUP BY t.challenge_count) hckcnt ON hckcnt.challenge_count = ch.challenge_count) p WHERE p.chlcnt IS NOT NULL ORDER BY p.chlcnt DESC, p.hacker_id

  • bimittaneja
     + 0 comments

    SQL server : With A1 as (select H.hacker_id,H.name,count(Challenge_id)cnt from Hackers H join Challenges C on H.hacker_id=C.Hacker_id group by H.hacker_id,H.name), A2 as (select *,dense_rank() over(order by cnt desc) rnk, lag(cnt) over(order by cnt desc) lgcnt,lead(cnt) over(order by cnt desc) ldcnt from A1) ,A3 as (select Hacker_id,name,cnt from A2 where rnk=1 union select Hacker_id,name,cnt from A2 where rnk>1 and case when lgcnt=cnt or ldcnt=cnt then 'no' else 'yes' end='yes' ) select * from A3 order by 3 desc;