basaks 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 Hi, in the 1st line, why name and sum(score) don't need to max_score.XXXX?
forjon May the column "score" is unique in the result table
jiangtaoyeah '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 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 Group By is different than order by. Order by is what determines sort.
arushiagarwal48 Can u pls explain what is max_score?
johnathan_b_ste1 max_score is a temporary table with 2 columns: hacker_id and score where score is the MAX(Submissions.score) the hacker got in each challenge as you can see by the GROUP BY clause. You can imagine max_score like this: challenge_1 -> list of hackers -> their respective max scores, challenge_2 -> list of hackers -> their respective max scores, ...
akshayl_28 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 question didnt ask for to find max_score? then why required query to find max_score? please explain?
pelias 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 [deleted]
jaynil60 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
carrielstylem why can't Julia just do these on herself :)
thippeswamydcts LOL.
kirakira 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
jpelsis 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 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 You could benefit from the use of aliases. :)
saitejal This one performs much better than other queries in this commend chain with a cost of 984.94
ajayvembu 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 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
pratibhakumar225 there wasn't necessity of two inner queries. SELECT Q.hacker_id, Q.name, SUM(Q.Max_score) AS total_score FROM ( SELECT h.hacker_id, h.name, s.challenge_id, MAX(s.score) AS Max_score FROM hackers AS h INNER JOIN submissions AS s ON h.hacker_id = s.hacker_id group by h.hacker_id, h.name, s.challenge_id ) AS Q group by Q.hacker_id, Q.name having SUM(Q.Max_score) <> 0 ORDER BY total_score DESC, Q.hacker_id;
kmalysiak_pl Nice, at MS SQL just a little bit slower than with 2 subqueries (at least at estimated execution plan)
yogesh_joshi 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 AWESOME QUERY! THANK YOU! REALLY GREAT
jeffdropsit sweet...
jwu112 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 Awesome solution. well formatted. thanks
memos [deleted]anirudh3_kumar 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 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 If MAX SCORE is 0 the total score has to be 0.
hypnoglow ANSI Join syntax is preferred: https://stackoverflow.com/questions/1599050/ansi-vs-non-ansi-sql-join-syntax
rajendra_aralik1 add group by cluse h.name also
raj_hassani you need to add h.name in group by clause in the outside query
14102257ayush the solution is almost correct, the only thing u need is to add a h.name in group by clause
rajeshksethi 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 You are life Savior :)
aleshia38 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;
DrLeport Can anyone tell me why this My SQL code doesn't work?
SELECT s.hacker_id, h.name, sum(s.score) FROM Hackers as h INNER JOIN Submissions as s ON h.hacker_id = s.hacker_id WHERE s.score IN (SELECT MAX(score) FROM Submissions GROUP BY hacker_id, challenge_id ORDER BY hacker_id) GROUP BY s.hacker_id, h.name HAVING sum(s.score) >0 ORDER BY sum(s.score) desc, s.hacker_id asc
superstj I took the same approach. Seems like creating a temp table to inner join with yields different result from using a subquery to select the max value. Many people have the same question.
lkgzkkl /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;
kcharania MySQL query:
SELECT HACKERS.HACKER_ID,HACKERS.NAME,SUM(SUBMISSIONS.SCORE) FROM HACKERS INNER JOIN (SELECT SUBMISSIONS.CHALLENGE_ID,SUBMISSIONS.HACKER_ID,MAX(SUBMISSIONS.SCORE) AS SCORE FROM SUBMISSIONS GROUP BY SUBMISSIONS.CHALLENGE_ID,SUBMISSIONS.HACKER_ID ORDER BY SUBMISSIONS.HACKER_ID DESC)SUBMISSIONS ON HACKERS.HACKER_ID = SUBMISSIONS.HACKER_ID GROUP BY HACKERS.HACKER_ID, HACKERS.NAME HAVING SUM(SUBMISSIONS.SCORE) >0 ORDER BY SUM(SUBMISSIONS.SCORE) DESC, HACKERS.HACKER_ID ASC
Sort 297 Discussions, By:
Please Login in order to post a comment