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

Nice code easy and understanable thanks alot

why groupby?

none of these codes are working here

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;

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

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

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

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

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!

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?

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;

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

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

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

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

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

c.difficulty_level = d.difficulty_level

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

I think the "c.difficulty_level = d.difficulty_level" in WHERE is no need, since we have connect the table c and table d using the difficulty_level column

Why did we not use c.hacker_id?

I had to do this to run in oracle. Oracle doesn't allow to group by without having aggregate function in select statement.

select hacker_id, name from 
(
select h.hacker_id, h.name, count(h.hacker_id)
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);

I was confused in this part at first.

thank you nice observation

This seems to be a valid approach.

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

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

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

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

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

thanks i missed the group by clause

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

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?

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.

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?

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.

what does count(1) mean here ?

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

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
;

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.

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