sevenzeni MySQL code
select h.hacker_id, h.name from submissions s inner join challenges c on s.challenge_id = c.challenge_id inner join difficulty d on c.difficulty_level = d.difficulty_level inner join hackers h on s.hacker_id = h.hacker_id where s.score = d.score and c.difficulty_level = d.difficulty_level group by h.hacker_id, h.name having count(s.hacker_id) > 1 order by count(s.hacker_id) desc, s.hacker_id asc
pbhurke1990 This is a good code to understand use of joins, thanks!
ATMaher Agreed! Thanks sevenzeni :) Some of your code is redundant btw, here is a cleaned up version:
SELECT h.hacker_id, h.name FROM submissions s JOIN challenges c ON s.challenge_id = c.challenge_id JOIN difficulty d ON c.difficulty_level = d.difficulty_level JOIN hackers h ON s.hacker_id = h.hacker_id WHERE s.score = d.score AND c.difficulty_level = d.difficulty_level GROUP BY h.hacker_id HAVING COUNT(s.hacker_id) > 1 ORDER BY COUNT(s.hacker_id) DESC, s.hacker_id ASC
gustav_danell I came up with the exact solution,except my JOINs are in a different order. Using both my solution and copy pastying this solution I get the same error:
Column 'hackers.name' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Could someone please shed some light into this? Also, is the "c.difficulty_level = d.difficulty_level" in the WHERE clause neccessary? Isn't that "taken care of" from the JOIN condition?
EDIT: Changing to MySQL resulted in a working code.
ashishgusain52 yes same for me, also can you please explain why we did s.score=d.score? since by doing that we will be removing some scores like 77, 47 from the submissions table.
ianlee313 Cause it's asking for "full score", those submission without having a full score will not be considered.
ukhandait s.score is the score of an individual d.score is the FULL score of that difficulty level. As the questions asks about only those who scored Full Marks thus s.score= d.score. It only gives the table of those people who got full scores only.
survivalkit when you do qroup by you need to pull all the things you select e.g. select h.hacker_id, h.name
your group by would be group by h.hacker_id, h.name
idofshivam you cannot
selectacolumnif it is not ingroup byclause. Therefore you need togroup byusing the bothhackers.hacker_id, name.
sudhar287 But there are different hacker_id with same name. Why would we need to group by with name also?
EDIT:
Is it because we first group by hacker_id and then by hacker name...? So nothing goes out of order...
dhruvkalaria AND c.difficulty_level = d.difficulty_level
line is redundant. You can remove it
flowfelis can someone explain why use GROUP BY? Thanks
8players Most, if not all, hackers in the submissions table have multiple entries. GROUP BY allows you to COUNT the entries that meet the WHERE conditions.
jasontangfocus if not using group by clause, then having clause will not work
edlaierii Having without Group By . Actually it does but returns 1 or 0 based on the system you run it on, that a having with a group by is like group by () which is implicit.
edlaierii All you did ARMaher was take of his 'inner' wording
vamshi_peesari Edlaierii, can you please tell why this code does not wrok for Oracle.
tilper And removed a redundant "and" clause.
jasontangfocus I think we can put the where condition into the join on clauses
edlaierii The ability to do something doesn't it make it the best or right way to do it though. The 'on' clauses are what we base our table joins on. The where clause is a filter on the joined tables and as such should be the proper place for conditions set on the resultSet.
vishalsingh_vs08 i guess it would work without this condition as well since the join has already been created under this condition.
c.difficulty_level = d.difficulty_level(in the where clause)
lleiou I‘m sorry, this code will have an error since it only groups by the h.hacker_id, you should also group by name. Best,
sharma_s Hi! Thanks for sharing the solution. I am a beginner at learning SQL. I can't understand why we are selecting from submissions instead of Hackers. Can anybody guide me here? Thanks
Hana1234 You can select from hacker too:
select s.hacker_id, h.name from hackers as h inner join submissions as s on h.hacker_id = s.hacker_id inner join challenges as c on s.challenge_id = c.challenge_id inner join difficulty as d on c.difficulty_level = d.difficulty_level where s.score = d.score and c.difficulty_level = d.difficulty_level group by h.hacker_id, h.name having count(h.hacker_id) > 1 order by count(h.hacker_id) desc, h.hacker_id;
sirongli322 Could anyone exlain why use h.hacker_id instead of Hackers.hacker_id? If you did not show that Hackers as h, how could you use h. to represents Table Hackers?
chirag0321 [deleted]
ameykpatel What's wrong with this code? INNER JOIN ,being commutative, should work even if the order of the join statements is altered.,Shouldn't it?
select s.hacker_id,h.name from hackers as h inner join challenges as c on h.hacker_id=c.hacker_id
inner join difficulty as d on d.difficulty_level=c.difficulty_level inner join submissions as s on s.challenge_id=c.challenge_id where d.score=s.score and c.difficulty_level= group by s.hacker_id,h.name having count(s.hacker_id)>1 order by count(s.hacker_id) desc,s.hacker_id;dastaan93 The last order by should be
h.hacker_id asc
and not
s.hacker_id asc
shashank_singh3 select h.name,s.hacker_id,s.score from Submissions s join Challenges c on s.challenge_id = c.challenge_id join Difficulty d on d.difficulty_level = c.difficulty_level join Hackers h on h.hacker_id = s.hacker_id where d.score = s.score group by s.hacker_id,s.score,h.name having count(s.hacker_id) > 1 order by s.score desc,s.hacker_id asc;
shashank_singh3 select z.hacker_id,z.name from (select h.name,h.hacker_id from Submissions s inner join Challenges c on s.challenge_id = c.challenge_id inner join Difficulty d on d.difficulty_level = c.difficulty_level inner join Hackers h on h.hacker_id = s.hacker_id and d.score = s.score group by h.hacker_id,h.name having count(h.hacker_id) > 1 order by h.hacker_id asc)z;
msidargo why do you pick select h.hacker_id, h.name from submission?
evazizaifeihua I have almost the same code as yours except that I join submissions and challenges tables on two coditions: s.challenge_id = c.challenge_id and a.hacker_id = c.hacker_id. I did so because chanllenges table does not suggest difficulty_level is related to chanllenge_id only, it might be related to tracker_id as it shows in sample table. I think my code is more conservative in this sense. However, it gives wrong output. The question needs to either remove tracker_id column from challenge table or specify challenge_id is unique key or make the real challenge table complete with all tracker_id conditions.
imr3ivaj SELECT h.hacker_id, h.name FROM hackers as h JOIN (submissions as s, challenges as c, difficulty as d) ON ( s.hacker_id = h.hacker_id AND s.challenge_id = c.challenge_id AND c.difficulty_level = d.difficulty_level ) WHERE s.score = d.score GROUP BY h.hacker_id, h.name HAVING COUNT(*) > 1 ORDER BY COUNT(*) DESC, h.hacker_id
DexterousLOL THE ABOVE CODE DOESNT WORK IN MYSQL IT GIVES AN ERROR
ashokkhote93 Hi, I have a question Does order of joining table matters?
g_dileep92 why are we imposing the condition c.difficulty_level = d.difficulty_level in the where clause again? Is this not redundant? As we are already using this condition in our join. And can someone explain why the order of joins important? because I tried with exact same solution but different order of joins and I got a wrong answer.
saisowhit Dude even the below code is also correct
select hacker_id,name from hackers h where h.hacker_id in(select hacker_id from challenges where score in(select max(score) from submissions))group by h.hacker_id,having count(h.hacker_id)>1 order by h.hacker_id asc;
but generating errors
RAHUL94JHA AND c.difficulty_level = d.difficulty_level
this condition check is not needed as challenge and difficulty tables are being joined on this condition only.
punitdabhi007 Nice code easy and understanable thanks alot
narayan_bang2358 why groupby?
pugaliaavichal none of these codes are working here
tanayz SQL Server version
select h.hacker_id, h.name from submissions s inner join challenges c on s.challenge_id = c.challenge_id inner join difficulty d on c.difficulty_level = d.difficulty_level inner join hackers h on s.hacker_id = h.hacker_id where s.score = d.score group by h.hacker_id, h.name having count(h.hacker_id) > 1 order by count(h.hacker_id) desc, h.hacker_id asc;
syk_ctrj This works in ORACLE !! Thanks.
Vasanth_Kumar12 [deleted]Vasanth_Kumar12 Can you say how it works coz it looks like a server query to me, but it works in Oracle. How?!?!?!?
mail2me_barath This works!!:)
vijayraj34 [deleted]yangforbig Cleaned one that works
SELECT s.hacker_id, h.name FROM Submissions S JOIN Challenges C ON S.challenge_id = C.challenge_id JOIN Difficulty D ON C.difficulty_level = D.difficulty_level JOIN Hackers H ON S.hacker_id = H.hacker_id WHERE S.score = d.score GROUP BY h.hacker_id, h.name HAVING COUNT(s.hacker_id) > 1 ORDER BY COUNT(s.hacker_id) DESC, s.hacker_id
leonardoanjos Although it is a not very advanced level query you have helped a lot in what are starting in sql. congratulations
aarill_123 /** In the where clause : 'score' corresponds to 'difficulty_level' thus checking s.score = d.score and c.difficulty = d.difficulty is redundant as tables are already joined on difficulty level . We could only check s.score = d.score . Correct me if I am wrong.
select h.hacker_id, h.name from submissions s inner join challenges c on s.challenge_id = c.challenge_id inner join difficulty d on c.difficulty_level = d.difficulty_level inner join hackers h on s.hacker_id = h.hacker_id where s.score = d.score /*and c.difficulty_level = d.difficulty_level*/ group by h.hacker_id, h.name having count(s.hacker_id) > 1 order by count(s.hacker_id) desc, s.hacker_id asc
tilper c.difficulty_level = d.difficulty_level is needed because d.score = s.score by itself is not enough to guarantee the score is full. For example, if difficulty level 2 has max score 40 and difficulty level 1 has max score 20 then d.score = s.score would return a hit on a submitted score of 20 for a difficulty level 2 question, even though that's not a max score for difficulty level 2.
However, the commenting out as you did is still fine because what you commented out is redundant. It's already being handled in the join clause.
danielamador Some of these are poorly written, I had no idea difficulty level determined "full score" whatever the hell that means. That was the one part I was stuck on. It never says score must match that of difficulty level.
tilper It could've been phrased more clearly for sure (calling the score column "max_score" in the difficulty table, for example) but I think it's not too hard to decipher from the context what meant, especially after examining the tables.
chirag0321 Why do we even need to use GROUP BY in the above code. I know that it will give an error but I'm still confused as to why group by is used. Plus wont count(s.hacker_id) count those submissions which have not received full marks also...
rajendra_dono in the last line ..it will be h.hacker_id asc as it is asked in the questions.
aeshen13 hi, i am wondering what this line does: where s.score = d.score and c.difficulty_level = d.difficulty_level if we are using inner join, why do we have to be sure that the score matches and the difficulty level matches across the join? thanks!
shashank_singh3 Its as per problem stated.
gregafen Not sure if i am correct but if you have a hacker which submitted multiple times to the same competition with a full score then with this query you will falsely select him in the top positions right? Wouldn't it be better if in the same exact query we had HAVING COUNT(DISTINCT s.challenge_id)>1 instead?
vkp92 no diff between yours and mine but mine is not passing the test cases. please review
select a.hacker_id, a.name from hackers a, difficulty b, challenges c, submissions d where c.challenge_id = d.challenge_id and a.hacker_id = d.hacker_id and b.difficulty_level = c.difficulty_level and b.score = d.score group by a.hacker_id,a.name having count(a.hacker_id) >1 order by count(a.hacker_id) desc, a.hacker_id;
craig_hughes Why is difficulty level included when there is no reference to it in the question?
swathisagi1993 this doesnt work for me. the code is: select a.hacker_id, a.name from (select name, s.hacker_id from submissions s join challenges c on s.challenge_id = c.challenge_id inner join difficulty d on c.difficulty_level = d.difficulty_level inner join hackers h on s.hacker_id = h.hacker_id
where s.score = d.score and c.difficulty_level = d.difficulty_level) a group by a.hacker_id, a.name
having count(a.hacker_id) >1 order by count(a.hacker_id) desc , a.hacker_idjhli_potomac I have an extra line code after "on s.challenge_id = c.challenge_id"
AND s.hacker_id = c.hacker_id
it does not return any record, can you explain. I thought it more strigent
grange_tuwien Guys,
I messed it up because I didn't carefully read the table description. challenges.hacker_id is the id of the hacker that CREATED the challenge, not the id of the guy that participated in it.
Take care of that, because this join condition ruins everything ;)
pradipkharbuja I was confused in this part at first.
MSHRI628 thank you nice observation
tianleihou SQL Solution, hope it helps. The main thing is using having
/* join tables together, to make a master table which contains all the info */ select sub.hacker_id, hak.name from submissions sub join challenges cha on sub.challenge_id = cha.challenge_id join hackers hak on sub.hacker_id = hak.hacker_id join difficulty dif on cha.difficulty_level = dif.difficulty_level
/* filter logic, to eliminate submissions that did not earn full score */ where dif.score = sub.score
/* further eliminate hackers who only had one full-score submission */ group by sub.hacker_id, hak.name having count(sub.score) > 1
/* display by the order stated in the proble, */ order by count(sub.score) desc, sub.hacker_id
tmartiAsked to answer This seems to be a valid approach.
ashwaryajethi Can you explain why have you used groupby hak.name here ?
azam58 Because he has selected both hacker_id and name. (select sub.hacker_id, hak.name). If he were not to group by name as well, then it would be an error unless you use max(name) while selecting. See Reason for Column is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause [duplicate]https://stackoverflow.com/a/13999903/3370168
nikchow please correct me where i'm going wrong. select h.hacker_id,h.name from submissions s join challenges c on s.hacker_id=c.hacker_id join hackers h on h.hacker_id = c.hacker_id join difficulty d on d.difficulty_level = c.difficulty_level where d.score = s.score and c.difficulty_level = d.difficulty_level group by h.hacker_id,h.name having count(s.hacker_id)>1 order by count(s.hacker_id)desc, h.hacker_id asc
LinVenus c.difficulty_level = d.difficulty_level in where d.score = s.score and c.difficulty_level = d.difficulty_level is repeated by you.
LinVenus Appreciate it!!!!I did it by this approach. Thank you for sharing.
victoria_andrada Hi tianleihou. Why dif.score must be equal to sub.score? wouldn't be dif.score >= sub.score valid too?
hartmann_stl Assuming there is no extra credit for assignments, >= and = would be equivalent.
shivam9544 thanks i missed the group by clause
JDenman6 Plus two for adding comments that explain what the code does :)
hartmann_stl Firstly thank you for your submission. I am trying to understand the syntax sub.hacker_id and hak.name. sub and hak look like some type of user-defined variables. I am a little bit confounded as to how this works. Do you know of a good tutorial or documentation to understand this better?
andrew_w_smith I'M YELLING BECAUSE IT IS RIDICULOUS THAT A SCORE GREATER THAN A FULL SCORE IS NOT A "FULL SCORE"
It's like getting extra credit is crap, it's like the losing team in soccer getting more goals. It's like.... WTF man.
Upvote this to support your fellow hackers and spread the word!
cornerkitten After seeing your message as a sanity check, I put in an edit suggestion. Seems that either:
- The data needs cleaned or
- There needs to be clarification for how to handle higher-than-max scores.
Thanks for letting me know I'm not crazy.
maciejstanek The same problem here.
Example:
- submission with submission_id = 7884 has challenge_id = 99326 and score = 120
- challenge with challenge_id = 99326 has difficulty_level = 5
- difficulty_level = 5 has (max) score = 80
Therefore someone got 120/80 points.
WTF?
kiradotee Man, thanks. THAT WAS IT!
I've spent ages trying to figure out what's wrong and it's the
>=sign, who would have thought that scoring more does not give you a full score! WTF.Peace.
jschulte10 ORACLE: select hacker_id, name from ( select s.hacker_id, h.name, count(1) cnt from submissions s inner join hackers h on s.hacker_id = h.hacker_id inner join challenges c on s.challenge_id = c.challenge_id inner join difficulty d on c.difficulty_level = d.difficulty_level and s.score = d.score group by s.hacker_id, h.name)a where cnt > 1 order by cnt desc, hacker_id;
sahilkumar1012 what does count(1) mean here ?
jschulte10 count(1) is simply a count of rows. it does not matter what you put inside the count function. count(1) = count (*) = count('foo').
krvajal_miguela1 It does matter because if you put for example count(name) in a table with a column called names, it will count only the values that are not null.
jschulte10 You are definitely correct. All the examples I mentioned above do not count the values in a specific column, but rather counts all rows in the GROUP BY regardless of weather certain columns contain null values. I'm simply passing an arbratrary value into the COUNT() function (ie 1 or 'foo') which will count 1 for each row. It is great to point out, as you did, that counting a specific column rather than the row itself will ignore any null values on that column. When you don't care about null values in certain columns, I would avoid passing a specific column into your COUNT() to avoid under-counting due to nulls.
margaretstrauss what does the last line a where cnt > 1...what does the a do because the code doesn't run without it
san28v My solution in SQL Server:
SELECT CONCAT(CAST(hacker_id AS VARCHAR), SPACE(2), name) FROM ( SELECT S.hacker_id AS [hacker_id], H.name AS [name], COUNT(S.challenge_id) AS [Total_Challenges] FROM Submissions S INNER JOIN Challenges C ON S.challenge_id = C.challenge_id INNER JOIN Hackers H ON H.hacker_id = S.hacker_id INNER JOIN Difficulty D ON D.difficulty_level = C.difficulty_level AND S.score = D.score GROUP BY S.hacker_id, H.name HAVING COUNT(S.challenge_id) > 1 ) AS Derived_Table ORDER BY Total_Challenges DESC, hacker_id ASC
charmon79 While that yields a correct-ish result, a couple of critiques:
- The spec didn't call for concatenation in the result set, so technically this didn't meet the requirement - but the test let you squeak by.
- Avoid expressing filtering conditions in JOIN predicate. JOIN predicate should involve key columns only. (Use WHERE to filter for full scores only. score is not a key in Submissions.)
Alternative solution, a bit more straightforward & easier to read:
SELECT h.hacker_id , h.name FROM Hackers AS h JOIN Submissions AS s ON s.hacker_id = h.hacker_id JOIN Challenges AS c ON c.challenge_id = s.challenge_id JOIN Difficulty AS d ON d.difficulty_level = c.difficulty_level WHERE s.score = d.score GROUP BY h.hacker_id , h.name HAVING COUNT(1) > 1 ORDER BY COUNT(1) DESC , h.hacker_id ASC ;
zhaoxiaq Do you know whether the following code is correct or not? It also pass the online test. thanks!
SELECT h.hacker_id, h.name 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 c.difficulty_level = d.difficulty_level WHERE s.score = d.score GROUP BY 1,2 HAVING COUNT(DISTINCT c.challenge_id) > 1 ORDER BY COUNT(DISTINCT c.challenge_id) DESC, h.hacker_id ASC
zhaoxiaq Is it because the Group by is operated on h.hacker_id and h.name, so that the COUNT can only operate on these two features?
charmon79 That query doesn't pass on Microsoft SQL at least, because you can't use ordinal position numbers in the GROUP BY clause. (Those only work in ORDER BY, and even then, it's a bad idea to use them.)
Msg 164, Level 15, State 1, Server WIN-ILO9GLLB9J0, Line 8 Each GROUP BY expression must contain at least one column that is not an outer reference.
MSHRI628 Most simple sql
select h.hacker_id,h.name from hackers h,challenges c ,difficulty d,submissions s where h.hacker_id=s.hacker_id and c.challenge_id=s.challenge_id and c.difficulty_level=d.difficulty_level and s.score=d.score group by h.hacker_id,h.name having count(h.hacker_id)>1 order by count(c.challenge_id) desc,h.hacker_id
bungerh Here's mine in Oracle, simplest way it seems : Hacker_id in challenges correspond to hackers who created them so watch out !
select h.hacker_id, h.name--,count(distinct s.submission_id) from hackers h join Submissions s on h.hacker_id = s.hacker_id join Difficulty d on s.score = d.score join Challenges c on s.challenge_id = c.challenge_id and d.difficulty_level = c.difficulty_level group by h.hacker_id, h.name having count(s.submission_id) > 1 order by count(s.submission_id) desc, hacker_id asc;
thebick Couldn't have done it without you. This helped me learn:
- about aliases, and that once you alias, the old table name is GONE;
- SQL is an extremely finicky language, and that the diagnostics are often useless;
- about the comment operator "--";
- trying to add extra columns to the output during testing screws everything up;
- and that if I want to limit the output to N lines after all the group by and order by, I have to nest select
I'm surprised I have any hair left. And I'm not even on Advanced Join yet.
v1nhc i hope with help!
select sub.hacker_id, hak.name from submissions sub, challenges cha, hackers hak, difficulty dif where sub.challenge_id = cha.challenge_id and sub.hacker_id = hak.hacker_id and cha.difficulty_level = dif.difficulty_level /*to eliminate submissions that did not earn full score */ and dif.score = sub.score /* further eliminate hackers who only had one full-score submission */ group by sub.hacker_id, hak.name having count(sub.score) > 1 /* display by the order stated in the proble, */ order by count(sub.score) desc, sub.hacker_id;
sgnk93 Can you please clarify why it is necessary to use group by on both sub.hacker_id and hak.name?
shitanshu_kumar Because you cannot select hacker_id and Hak.name at once if group by is on a single column.We can select only functions and column in a Query if group by is used on a single column.
tucaoneo Yeah, I had the same question. I think I understand now. We thought it would be OK to group by id only because we KNEW that id and name are uniquely paired to each other. However sql does not know that and cannot assume that. If we group by id only, then from sql's point of view, it may well be possible that multiple different names corresponding to the same id are counted together. Therefore sql cannot print a separate column for name.
rajeshksethi Oracle Version:
select hacker_id, name from (select s.hacker_id, h.name, count(1) cnt from submissions s inner join hackers h on s.hacker_id = h.hacker_id inner join challenges c on s.challenge_id = c.challenge_id inner join difficulty d on c.difficulty_level = d.difficulty_level and s.score = d.score group by s.hacker_id, h.name) a where cnt > 1 order by cnt desc, hacker_id;
Sort 306 Discussions, By:
Please Login in order to post a comment