1

I could not solve this problem on hackerrank , and had to look up the solution. I was joining the wrong tables.

There are 4 tables:

  • hackers
  • challenges
  • submissions made by hackers and their scores
  • difficulty table with levels.

the task description:

Write a query to print the respective hacker_id and name of hackers who achieved full scores for more than one challenge.

Submissions table fields: submission_id, hacker_id, challenge_id, score

challenge table fields: challenge_id, hacker_id, difficulty_level

The way i joined: hackers + challenges, challenges + difficulty , challenges + submissions.

select  
hackers.hacker_id, name
 
from submissions inner join challenges on submissions.challenge_id = challenges.challenge_id 
inner join difficulty on difficulty.difficulty_level = challenges.difficulty_level
inner join hackers on 

challenges.hacker_id = hackers.hacker_id -- here is wrong part!

where difficulty.score = submissions.score and difficulty.difficulty_level = challenges.difficulty_level
group by hackers.hacker_id, name 
having count( challenges.challenge_id)> 1
order by count( challenges.challenge_id) desc, hackers.hacker_id 

However, the right way was almost same - except that i should have joined submissions + hackers by hacker_id vs not submissions + challenges by challenge_id.

The correct way:

select  
hackers.hacker_id,name

from submissions inner join challenges on submissions.challenge_id = challenges.challenge_id 
inner join difficulty on difficulty.difficulty_level = challenges.difficulty_level
inner join hackers on submissions.hacker_id = hackers.hacker_id
where difficulty.score = submissions.score and difficulty.difficulty_level = challenges.difficulty_level
group by hackers.hacker_id, name 
having count( challenges.challenge_id)> 1
order by count( challenges.challenge_id) desc, hackers.hacker_id 

What's the logic behind joining submissions + challenges by challenge_id vs submissions + hackers by hacker_id?

why does it produce different result? A hacker makes submissions , so it should not matter whether i join challenge+submission + hackers or submission + hacker + challenge...

2
  • 1
    I could not solve this problem on hackerrank Wrong link... anycase this is bad practice - post complete task definition instead of link. .. table fields: Tales are not useful - provide CREATE TABLE for all tables including indices and FKs. Also provide data statistic for all tables - total and join ciondition matches rows count at least...
    – Akina
    Mar 5 at 4:51
  • @Akina , thx i updated the link
    – ERJAN
    Mar 5 at 9:42
1

It's a bit of a guess because you haven't provided the link to the challenge (in markdown you have to make links like [this problem](http://whatever.com) but clicking your link just takes to http://this%20problem, so I guess the markdown is broken

You've said you have tables:

Hacker - I guess this is like a user table
Challenge - I guess this is a problem to solve
Submission - I guess it's an answer a hacker has made to a problem

So we can clearly have "one hacker may answer many challenges" and "one challenge has many hackers answering it" ie a many many relationship so we need a table in the middle to split it down and track which hacker answered which challenge, and this is the Submissions table doing this breakdown

This interesting part here, and possibly put in deliberately to trip people up, is that Challenge also has a hacker Id column. For some reason hacker rank's tables track a hacker-challenge relationship that is nothing to do with a submission ie the hacker ID in the challenge table is something where there can be only one hacker for that challenge, such as the hacker that created it, or the hacker that first got perfect score on it, or the hacker who is responsible for marking the answers on it, or the hacker that most recently took it..

Whatever the reason, you said you joined on that hacker ID which in my mind is the wrong thing to do because it bears no relation to the question of multiple hackers who have submitted: it could be that the hacker in the challenge.hacker_id has never even made a submission on the challenge, they simply wrote it and have never answered it. It therefore cannot give you the answers the challenge seeks. It's probably meant to catch you out and it's one reason why when I'm designing a database schema I aim to put the reason for a column into its name in cases like this; ie in the Challenge table I might have called it author_hackerid or moderator_hackerid to describe not only where it links to (hacker table on hacker ID) but why it links there (author, moderator, firstwinner, recenttaker) especially if there are multiple people.. (though in such a case it may make more sense to have another table, like submission) that splits hacker and challenge, and gives a reason for why that hacker is related to that challenge)

Remember, that just because two columns are named the same it doesn't mean they are alternative join options. Even columns that have participated in a join before are not alternative, if it's a left join. If it's an inner join, fair game:

a INNER JOIN b ON a.id = b.id INNER JOIN c ON b.id = c.id
a INNER JOIN b ON a.id = b.id INNER JOIN c ON a.id = c.id
                                              ^

but these can give different results:

a LEFT JOIN b ON a.id = b.id LEFT JOIN c ON b.id = c.id
a LEFT JOIN b ON a.id = b.id LEFT JOIN c ON a.id = c.id

That small but critical difference in the second query of both tables left joining to a might be huge

7
  • i did bad when not checked the url validity - yes i updated it , you can read the description. thx for pointing out to the hacker_id in challenge vs hacker_id in submission.. i did not think it could be another role - first taker, recent taker..
    – ERJAN
    Mar 5 at 9:46
  • 1
    OK, so now you updated the link i can read "the hacker_id [in the challenge table] is the id of the hacker who created the challenge", so I'll let my answer stand
    – Caius Jard
    Mar 5 at 10:10
  • thx, but still - i wanna explain this to myself why my join of challenges + submissions on challenge_id was wrong. It was wrong cuz it produced the wrong table , right? naturally - it should be hackers + submissions - aka "hackers joined with their submissions"
    – ERJAN
    Mar 5 at 10:14
  • 1
    Is there anything you don't understand after having read my answer? You know what you should have done (you posted it in the question), you know what you did (you also posted and know where it is wrong) and you asked why it was wrong - the short version of my answer is "because challenge.hacker_id is the ONE hacker that made the challenge, which is nothing to do with the MANY hackers who solved it"
    – Caius Jard
    Mar 5 at 11:07
  • jard thx u so much, this short answer is very on point! thx , i really was looking for this kinda explanation
    – ERJAN
    Mar 5 at 11:20

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.