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

|

2543 Discussions

|

  • uladzimir_charn1
     + 0 comments

    A bit different from other posted solutions: 

    SELECT
    t.hacker_id,
    t.name
FROM 
(SELECT 
    h.name,
    h.hacker_id,
    CASE WHEN s.score * 1.0 / d.score = 1 THEN 1 ELSE 0 END AS is_full
FROM Submissions s
JOIN Hackers h ON s.hacker_id = h.hacker_id
JOIN Challenges c ON c.challenge_id = s.challenge_id
JOIN Difficulty d ON d.difficulty_level = c.difficulty_level
) t
GROUP BY t.hacker_id, t.name
HAVING SUM(t.is_full) > 1
ORDER BY SUM(t.is_full) DESC, t.hacker_id ASC;

  • mahmoudashrafza1
     + 0 comments

    Hey SQL Learners can Anyone told why this Query is wrong

    SELECT HCD.hacker_id, HCD.name  
FROM
    (SELECT H.hacker_id, H.name, C.challenge_id, D.difficulty_level, D.score
     FROM Hackers H
     JOIN Challenges C
        ON H.hacker_id = C.hacker_id
     JOIN Difficulty D
        ON D.difficulty_level = C.difficulty_level
    ) HCD
    
Inner JOIN Submissions S
    ON HCD.challenge_id = S.challenge_id
WHERE HCD.score = S.score
GROUP BY HCD.hacker_id, HCD.name
HAVING COUNT( S.submission_id) > 1
Order By COUNT(S.submission_id) > 1 DESC, HCD.hacker_id ASC
 ;

  • shashidhar0018
     + 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 d.score = s.score GROUP by h.hacker_id,h.name having count(distinct s.submission_id)>1 order by count(distinct s.submission_id) desc,hacker_id asc

  • 12018009019418H
     + 0 comments

    select hacker_id, name from (select a.hacker_id, name, count() as ct from (select s.hacker_id, c.challenge_id, d.score from Submissions s join Challenges c on s.challenge_id = c.challenge_id join Difficulty d on c.difficulty_level = d.difficulty_level) a join Submissions sub on a.hacker_id = sub.hacker_id and a.challenge_id = sub.challenge_id join Hackers h on a.hacker_id = h.hacker_id where a.score = sub.score group by 1,2 having count()>1 order by ct desc, hacker_id) ww

  • enlightenedgeedu
     + 0 comments
    SELECT
    h.hacker_id,
    h.name
FROM
    Hackers h
JOIN (
    SELECT
        s.hacker_id,
        s.challenge_id
    FROM
        Submissions s
        JOIN Challenges c ON s.challenge_id = c.challenge_id
        JOIN Difficulty d ON c.difficulty_level = d.difficulty_level
    GROUP BY
        s.hacker_id,
        s.challenge_id,
        d.score
    HAVING
        MAX(s.score) = d.score        
) full_scores
    ON h.hacker_id = full_scores.hacker_id
GROUP BY
    h.hacker_id,
    h.name
HAVING
    COUNT(full_scores.challenge_id) > 1       
ORDER BY
    COUNT(full_scores.challenge_id) DESC,
    h.hacker_id ASC;