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. Top Competitors
  5. Discussions

Top Competitors

Sort by

recency

|

2519 Discussions

|

  • Thilak05
     + 0 comments
    SELECT h.hacker_id, h.name
FROM hackers h
JOIN submissions s ON h.hacker_id = s.hacker_id
JOIN challenges c ON s.challenge_id = c.challenge_id
JOIN difficulty d ON c.difficulty_level = d.difficulty_level
WHERE s.score = d.score
GROUP BY h.hacker_id, h.name
HAVING COUNT(DISTINCT c.challenge_id) > 1
ORDER BY COUNT(DISTINCT c.challenge_id) DESC, h.hacker_id ASC;

  • krishna1627
     + 0 comments

    my sql : SELECT aaa.hacker_id, b.name FROM ( SELECT a.hacker_id, COUNT(DISTINCT a.challenge_id) AS totchal FROM submissions a LEFT JOIN challenges b ON a.challenge_id = b.challenge_id LEFT JOIN difficulty c ON b.difficulty_level = c.difficulty_level WHERE a.score = c.score GROUP BY a.hacker_id ) aaa LEFT JOIN hackers b ON aaa.hacker_id = b.hacker_id WHERE totchal > 1 ORDER BY totchal DESC, aaa.hacker_id ASC;

  • olorunsogoabiod1
     + 0 comments

    with cte as ( select
    c.challenge_id, c.difficulty_level, d.score as top_score from challenges c join difficulty d on c.difficulty_level = d.difficulty_level ), cte2 as( select s.hacker_id, s.challenge_id, s.score from submissions s inner join cte c on s.challenge_id = c.challenge_id and s.score = c.top_score ) select h.hacker_id, h.name from cte2 c join hackers h on c.hacker_id = h.hacker_id group by h.hacker_id, h.name having count(c.challenge_id) > 1 order by count(c.challenge_id) desc, hacker_id asc

  • snidhireddyg
     + 1 comment

    • why is this throwing an error: WITH cte1 AS( SELECT h.hacker_id AS id, h.name AS name, count(distinct s.challenge_id) FROM Hackers h JOIN Submissions s ON h.hacker_id = s.hacker_id JOIN Challenges c ON c.challenge_id = s.challenge_id JOIN Difficulty d ON d.difficulty_level = c.difficulty_level WHERE s.score = d.score ) SELECT id, name FROM cte1 GROUP BY id, name HAVING COUNT(DISTINCT challenge_id) > 1 ORDER BY COUNT(DISTINCT challenge_id) DESC, id ASC;

  • prekshashrm09
     + 0 comments

    why is this wrong:

    select h.hacker_id, h.name from Hackers h where count(*) from ( select s.hacker_id from submissions s join challenges c on c.challenge_id = s.challenge_id join difficulty d on d.difficulty_level = c.difficulty_level where d.score = s.score group by hacker_id, challenge_id )

    0 order by count(challenge) desc