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.
Loading...
  1. Practice
  2. SQL
  3. Basic Join
  4. Contest Leaderboard
  5. Discussions

Contest Leaderboard

Sort 262 Discussions, By:

votes

  • basaks + 7 comments
    select h.hacker_id, name, sum(score) as total_score
from
hackers as h inner join
/* find max_score*/
(select hacker_id,  max(score) as score from submissions group by challenge_id, hacker_id) max_score

on h.hacker_id=max_score.hacker_id
group by h.hacker_id, name

/* don't accept hackers with total_score=0 */
having total_score > 0

/* finally order as required */
order by total_score desc, h.hacker_id
;
    • xiaohui0209 + 2 comments

      Hi, in the 1st line, why name and sum(score) don't need to max_score.XXXX?

      • forjon + 0 comments

        May the column "score" is unique in the result table

      • jiangtaoyeah + 0 comments

        'name' is unique column name in 'Hackers' table; 'sum(score)': the 'score' here is actually the alias given by 'max(score)' in the subquery. So, no ambiguity here.

    • aditibansal_93 + 1 comment

      Can someone please explain why do we mention name along with h.hacker_id in group by clause? As we only have to sort as per hacker_id.

      • jenn_warner91 + 0 comments

        Group By is different than order by. Order by is what determines sort.

    • arushiagarwal48 + 0 comments

      Can u pls explain what is max_score?

    • akshayl_28 + 0 comments

      Oracle solution :

      select hacker_id,name,sum(a) from (select s.hacker_id,h.name,max(s.score) a from Submissions s inner join hackers h on h.hacker_id=s.hacker_id where s.score!=0 group by s.hacker_id,h.name,s.challenge_id) group by hacker_id,name order by 3 desc,1;

    • sabexab2009 + 1 comment

      question didnt ask for to find max_score? then why required query to find max_score? please explain?

      • pelias + 0 comments

        it looks like this is necessary because if a hacker submitted 2 scores for a single challenge, you only use the highest score when finding a total

    • erwinsurya09 + 0 comments
      [deleted]
    • jaynil60 + 0 comments

      select h.hacker_id, name, sum(score) from hackers as h inner join (select hacker_id, max(score) as score from submissions group by challenge_id, hacker_id) max_score on h.hacker_id=max_score.hacker_id group by h.hacker_id, name having sum(score) > 1 order by sum(score) desc, h.hacker_id;

      hy dear without alias name possible ? Actually it is run successfully in ms

  • kirakira + 0 comments

    MS SQL Version:

    SELECT h.hacker_id, h.name, SUM(score) FROM (
    SELECT hacker_id, challenge_id, MAX(score) AS score FROM SUBMISSIONS
    GROUP BY hacker_id, challenge_id
)t 
JOIN Hackers h on t.hacker_id = h.hacker_id
GROUP BY h.hacker_id, h.name
HAVING SUM(score) > 0
ORDER BY SUM(score) desc, h.hacker_id

  • carrielstylem + 0 comments

    why can't Julia just do these on herself :)

  • jpelsis + 3 comments

    Here is my MYSQL solution. Is this an efficient approach?

    SELECT
    t2.hid,
    h.name,
    t2.score
FROM
    (SELECT
        SUM(t1.maxscore) AS score,
        t1.hid as hid
    FROM
        (SELECT
            MAX(score) AS maxscore,
            challenge_id AS cid,
            hacker_id AS hid
        FROM
            submissions
        GROUP BY
            hacker_id,
            challenge_id) t1
    GROUP BY
        hid) t2
JOIN
    hackers h
ON h.hacker_id = t2.hid
HAVING t2.score > 0
ORDER BY
    t2.score DESC,
    t2.hid ASC;
    • jbdfr_dnph + 2 comments

      Hi,

      Indeed, can someone explain the differences between different approaches, with or without HAVING, with or without JOIN, etc? I have this:

      SELECT Hackers.Hacker_ID, Hackers.Name, SUM(smalltab.hscores) as sumscores FROM Hackers
JOIN
    (SELECT Submissions.Hacker_ID, MAX(Submissions.score) as hscores from Submissions
     GROUP BY Submissions.challenge_id, Submissions.hacker_id 
    ) as smalltab
on Hackers.Hacker_ID = smalltab.Hacker_ID
WHERE smalltab.hscores > 0
GROUP BY Hackers.Hacker_ID
ORDER BY sumscores DESC, hackers.hacker_id, hackers.name;
      • thestormcrow2 + 0 comments

        You could benefit from the use of aliases. :)

      • saitejal + 0 comments

        This one performs much better than other queries in this commend chain with a cost of 984.94

    • ajayvembu + 0 comments

      Here instead of the last join you do to get hacker name you could have retreived the hacker name from the first join itself, in that way you avoid one of the joins in the query.

    • saitejal + 0 comments
      SELECT
    t2.hid,
    h.name,
    t2.score
FROM
    (SELECT
        SUM(t1.maxscore) AS score,
        t1.hid as hid
    FROM
        (SELECT
            MAX(score) AS maxscore,
            challenge_id AS cid,
            hacker_id AS hid
        FROM
            submissions
	WHERE
		score > 0
        GROUP BY
            hacker_id,
            challenge_id) t1
    GROUP BY
        hid) t2
JOIN
    hackers h
ON h.hacker_id = t2.hid
ORDER BY
    t2.score DESC,
    t2.hid ASC;

      cost is 1232.55 compared to OPs query cost of 3685.0

  • yogesh_joshi + 2 comments

    Mysql

    select h.hacker_id, h.name, sum(scr) as fs from hackers as h join 
(select s.hacker_id, s.challenge_id, max(s.score) as scr from submissions as s
group by s.hacker_id, s.challenge_id having scr > 0) as t1   
on h.hacker_id = t1.hacker_id
group by h.hacker_id  
order by fs desc, h.hacker_id;
    • princejaideep10 + 0 comments

      AWESOME QUERY! THANK YOU! REALLY GREAT

    • jeffdropsit + 0 comments

      sweet...

  • rajeshksethi + 2 comments

    Oracle Version

    select hacker_id, name, sum(total_score) as total_score
  from (select s.hacker_id, name, challenge_id, max(score) as total_score
          from hackers h
          join submissions s
            on h.hacker_id = s.hacker_id
         where score > 0
         group by s.hacker_id, challenge_id, name)
 group by hacker_id, name
 order by total_score desc, hacker_id;
    • Sanjit_Prasad + 0 comments

      You are life Savior :)

    • aleshia38 + 0 comments

      What's wrong with this query? I kept getting errors:

      select h.name, h.hacker_id,h2.total_score from hackers h inner join (select hacker_id, (sum(score)-min(score)) as total_score from submissions group by hacker_id) h2 on h.hacker_id = h2.hacker_id where score >> 0 order by h2.total_score desc, h.hacker_id asc;

  • jwu112 + 8 comments

    MYSQL:

    SELECT h.hacker_id, h.name, 
sum(scr) as tot
FROM Hackers h, 
    (
        SELECT s2.hacker_id as hac_id, 
        s2.challenge_id as ch_id, 
        max(s2.score) as scr
        FROM Submissions s2
        GROUP BY s2.hacker_id,s2.challenge_id
        having scr > 0
    ) AS chart1
WHERE h.hacker_id = hac_id
GROUP BY h.hacker_id
ORDER BY tot DESC, h.hacker_id
    • chadwalt + 0 comments

      Awesome solution. well formatted. thanks

    • memos + 0 comments
      [deleted]
    • anirudh3_kumar + 0 comments

      I am confused as to which one is better in terms of performance?

      SELECT a.hacker_id, a.name, sum(a.score) score 
FROM (
    SELECT h.hacker_id hacker_id, h.name name, max(score) score 
    FROM Hackers h JOIN Submissions s ON h.hacker_id = s.hacker_id 
    GROUP BY hacker_id, challenge_id 
    HAVING score > 0
) a 
GROUP BY a.hacker_id 
ORDER BY score DESC, a.hacker_id;
    • tucaoneo + 1 comment

      The problem requires that you exclude any hacker whose TOTAL SCORE is 0. Your code excludes hackers whose MAX SCORE is 0 in any one challenge.

      • ankitchoudharyi1 + 0 comments

        If MAX SCORE is 0 the total score has to be 0.

    • hypnoglow + 0 comments

      ANSI Join syntax is preferred: https://stackoverflow.com/questions/1599050/ansi-vs-non-ansi-sql-join-syntax

    • rajendra_aralik1 + 0 comments

      add group by cluse h.name also

    • raj_hassani + 0 comments

      you need to add h.name in group by clause in the outside query

    • 14102257ayush + 0 comments

      the solution is almost correct, the only thing u need is to add a h.name in group by clause

  • lkgzkkl + 0 comments

    /MySQL solution/

    select hacker_id, name, sum(max_score) as total_score from (select h.hacker_id, name, s.challenge_id, max(s.score) as max_score from Hackers h join Submissions s on h.hacker_id = s.hacker_id group by h.hacker_id, name, s.challenge_id) t group by hacker_id, name having total_score > 0 order by total_score desc, hacker_id;

  • antarabasu + 0 comments

    SELECT hacker_id, NAME, SUM(score) FROM (SELECT h.hacker_id, h.name, s.challenge_id, MAX(s.score) AS score FROM hackers h, submissions s WHERE h.hacker_id = s.hacker_id GROUP BY h.hacker_id, NAME, s.challenge_id) X GROUP BY hacker_id, NAME HAVING SUM(score) > 0 ORDER BY SUM(score) DESC, hacker_id ASC ;

  • stazhenggy + 0 comments
    SELECT
    finalchallengescore.hacker_id AS hacker_id,
    name,
    total_score
FROM
(
    SELECT 
        hacker_id,
        challenge_id,
        max(score) AS finalscore,
        sum(max(score)) OVER (PARTITION BY hacker_id) AS total_score
    FROM
        Submissions
    GROUP BY 
        hacker_id,
        challenge_id
) finalchallengescore 
JOIN 
    Hackers
ON 
    finalchallengescore.hacker_id = Hackers.hacker_id
    AND total_score >= 1
GROUP BY
    finalchallengescore.hacker_id,
    name,
    total_score    
ORDER BY
    total_score DESC,
    hacker_id;

    Oracle with Window function

Load more conversations