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

Top Competitors

Sort 345 Discussions, By:

votes

  • sevenzeni + 20 comments

    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 + 1 comment

      This is a good code to understand use of joins, thanks!

      • ATMaher + 21 comments

        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 + 3 comments

          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 + 2 comments

            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 + 0 comments

              Cause it's asking for "full score", those submission without having a full score will not be considered.

            • ukhandait + 0 comments

              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 + 0 comments

            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 + 1 comment

            you cannot select a column if it is not in group by clause. Therefore you need to group by using the both hackers.hacker_id, name.

            • sudhar287 + 1 comment

              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...

              • rommell + 0 comments

                I don't see a need to group by Name theoretically unless there are multiple names for same Hacker ID. The test fails unless you group by Name. Without seeing what is in the test input, I really can't tell what this is about.

        • dhruvkalaria + 0 comments
          AND c.difficulty_level = d.difficulty_level

          line is redundant. You can remove it

        • flowfelis + 2 comments

          can someone explain why use GROUP BY? Thanks

          • 8players + 0 comments

            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 + 1 comment

            if not using group by clause, then having clause will not work

            • edlaierii + 0 comments

              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 + 2 comments

          All you did ARMaher was take of his 'inner' wording

          • vamshi_peesari + 0 comments

            Edlaierii, can you please tell why this code does not wrok for Oracle.

          • tilper + 0 comments

            And removed a redundant "and" clause.

        • jasontangfocus + 1 comment

          I think we can put the where condition into the join on clauses

          • edlaierii + 0 comments

            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 + 0 comments

          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 + 0 comments

          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 + 1 comment

          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 + 0 comments

            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 + 1 comment

          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?

        • ameykpatel + 0 comments

          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 + 2 comments

          The last order by should be 

          h.hacker_id asc

          and not 

           s.hacker_id asc
          • shashank_singh3 + 0 comments

            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 + 0 comments

            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 + 0 comments

          why do you pick select h.hacker_id, h.name from submission?

        • evazizaifeihua + 0 comments

          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 + 0 comments
          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 + 0 comments

          THE ABOVE CODE DOESNT WORK IN MYSQL IT GIVES AN ERROR

        • ashokkhote93 + 0 comments

          Hi, I have a question Does order of joining table matters?

        • g_dileep92 + 0 comments

          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 + 0 comments

          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 + 0 comments

          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.

        • anna_kor + 0 comments

          Great code, thank you! One thing to note is that if you change WHERE to AND in

              WHERE s.score = d.score

          and move it up right after the second JOIN...ON..., it should run faster, as it would use the condition in a join, not after all the joins. This is my version:

          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 
AND s.score = d.score 
JOIN Hackers h
ON s.hacker_id = h.hacker_id
GROUP BY h.hacker_id, h.name
HAVING count(h.hacker_id) > 1
ORDER BY count(h.hacker_id) DESC, h.hacker_id ASC;
        • jayushi_94 + 1 comment

          This COde doesn't work and throws error.

          sevenzeni's CODE WORKS!!!!

          • punitdabhi007 + 0 comments

            I have submitted this 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 I got the result. I cannot explain query because I have submiited this queries 2 years ago. Sorry for explainaton. I hope you understand.

    • punitdabhi007 + 0 comments

      Nice code easy and understanable thanks alot

    • narayan_bang2358 + 0 comments

      why groupby?

    • pugaliaavichal + 0 comments

      none of these codes are working here

    • tanayz + 6 comments

      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 + 0 comments

        This works in ORACLE !! Thanks.

      • Vasanth_Kumar12 + 0 comments
        [deleted]
      • Vasanth_Kumar12 + 0 comments

        Can you say how it works coz it looks like a server query to me, but it works in Oracle. How?!?!?!?

      • mail2me_barath + 0 comments

        This works!!:)

      • jdsimmons1 + 1 comment

        Can someone explain why it says count(h.hacker_id) instead of count(s.hacker_id)?

        I have tried both ways and they both work, I just do not understand why. Shouldn't count(h.hacker_id) just count how many rows are in the hackers table? Which I would assume would be one row per user?

        • punitdabhi007 + 0 comments

          "h" and "s" it is work as object.

      • adityakumar19951 + 0 comments

        Why u give condition here like (s.score = d.score)?

    • vijayraj34 + 0 comments
      [deleted]
    • yangforbig + 1 comment

      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 + 0 comments

      Although it is a not very advanced level query you have helped a lot in what are starting in sql. congratulations

    • aarill_123 + 1 comment
      /**
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 + 1 comment

        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 + 1 comment

          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 + 0 comments

            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 + 0 comments

      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 + 0 comments

      in the last line ..it will be h.hacker_id asc as it is asked in the questions.

    • aeshen13 + 1 comment

      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 + 0 comments

        Its as per problem stated.

    • gregafen + 0 comments

      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 + 0 comments

      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 + 0 comments

      Why is difficulty level included when there is no reference to it in the question?

    • swathisagi1993 + 0 comments

      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_id
    • jhli_potomac + 0 comments

      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

    • heisenberg_ab + 0 comments

      What is the difference if I write 'Join on' clause vs if I use simple 'where and' clauses to specify the conditions? Like I have done below, I have not used Join clause but rather used explicity where conditions, what difference does it make? Does it affect performance?

      select h.hacker_id, h.name
from hackers as h, submissions as s, difficulty as d, challenges as c
where
    s.score = d.score
    and s.challenge_id = c.challenge_id
    and c.difficulty_level = d.difficulty_level
    and s.hacker_id = h.hacker_id
group by h.hacker_id, h.name
having count(h.hacker_id) > 1
order by count(h.hacker_id) desc, h.hacker_id asc
    • saurabhpathak997 + 0 comments

      very good solution, but I didn't understand one thing why used again

      c.difficulty_level = d.difficulty_level

    • KhoaChau + 0 comments

      I don't understand why we must use having count(s.hacker_id) > 1

  • grange_tuwien + 2 comments

    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 + 0 comments

      I was confused in this part at first.

    • MSHRI628 + 0 comments

      thank you nice observation

  • tianleihou + 9 comments

    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 + 0 comments

      This seems to be a valid approach.

    • juanpaulo + 1 comment

      Very minor but I think grouping by hak.name is not necessary.

      • Peter_W + 0 comments

        That seems to depend on the specific database.

    • ashwaryajethi + 1 comment

      Can you explain why have you used groupby hak.name here ?

      • azam58 + 0 comments

        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 + 1 comment

      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 + 0 comments

        c.difficulty_level = d.difficulty_level in where d.score = s.score and c.difficulty_level = d.difficulty_level is repeated by you.

    • LinVenus + 0 comments

      Appreciate it!!!!I did it by this approach. Thank you for sharing.

    • victoria_andrada + 1 comment

      Hi tianleihou. Why dif.score must be equal to sub.score? wouldn't be dif.score >= sub.score valid too?

      • hartmann_stl + 0 comments

        Assuming there is no extra credit for assignments, >= and = would be equivalent.

    • shivam9544 + 0 comments

      thanks i missed the group by clause

    • JDenman6 + 0 comments

      Plus two for adding comments that explain what the code does :)

    • hartmann_stl + 0 comments

      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 + 3 comments

    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!

    My Solution)

    • cornerkitten + 0 comments

      After seeing your message as a sanity check, I put in an edit suggestion. Seems that either:

      1. The data needs cleaned or
      2. There needs to be clarification for how to handle higher-than-max scores.

      Thanks for letting me know I'm not crazy.

    • maciejstanek + 0 comments

      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 + 0 comments

      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 + 2 comments

    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 + 1 comment

      what does count(1) mean here ?

      • jschulte10 + 1 comment

        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 + 1 comment

          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 + 0 comments

            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 + 0 comments

      what does the last line a where cnt > 1...what does the a do because the code doesn't run without it

  • san28v + 1 comment

    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 + 1 comment

      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 + 2 comments

        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 + 0 comments

          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 + 0 comments

          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 + 0 comments

    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 + 1 comment

    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 + 0 comments

      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 + 1 comment

    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 + 2 comments

      Can you please clarify why it is necessary to use group by on both sub.hacker_id and hak.name?

      • shitanshu_kumar + 0 comments

        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 + 0 comments

        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.

  • aviknayak + 1 comment
    select s.hacker_id , h.name 
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
where s.score = d.score
group by s.hacker_id,h.name
having  count(h.name) > 1
order by count(h.name) desc , s.hacker_id asc
    • luisdavidshack + 0 comments

      I am aware people need help hacking something or someone contact Proffrankhack @ gmail for such service a lot of people have tried him and he worked perfectly

Load more conversations