Hackerrank SQL Solutions | Interviews SQL Hackerrank Hard Problem
ฝัง
- เผยแพร่เมื่อ 18 ก.ค. 2020
- Find the code to this problem: github.com/MarkusFriemann/Hac...
You find this problem at:
www.hackerrank.com/challenges...
____________________________________________
SUBSCRIBE!
Do you want to understand how to solve SQL Problems in every detail? That's what my channel is about. When I was younger I thought I could never program because it looked way too difficult. The truth is that it takes time but with some patience anybody can do it! Follow me along and get better!
____________________________________________
SUPPORT MY CHANNEL
🙌The best way to support my channel right now is to give me a Super Thanks. You can do that by clicking thanks next to the title of the video. It is much appreciated!
____________________________________________
💾GitHub: github.com/CuriosityLeonardo?...
✋Add me on LinkedIn to get in contact: / markus-friemann-221b3814b
It's amazing how you hacked your way to the correct answer, the excel debugging trick was especially cool.
Formatting, version control using notepad, copy pasting the CTE were all nice ideas.
I have 1 comment and 1 suggestion:
24:22-25:15 "In 1 SELECT statement, you can only go from contests table".
You can go from any table to any other table any number of times. If join A to B, the next table can be joined to A or B, not necessarily A. The 23:50 error was saying table names are duplicated. But even if you deduplicated them by changing to col2, it's still wrong because you joined Contests
Hi Han
Thanks for taking the time and writing this comment! You are absolutely right with your suggestions. You are helping me and others that can read this to further deepen our understanding of SQL. It is much appreciated!
Greetings
Markus
@@thecodingmentor7701
I copied this code from your link, and it is giving me syntax error.
" ERROR 1064 (42000) at line 1: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'total_submit as
( SELECT
coll.contest_id,
SUM(ss.tot' at line 1". I googled the syntax of with clause also, but it is correct. Can you please tell me what the problem is
just love how some people really love to do their work and are passionate about it and are not here on youtube just for views. you are one of them. thank you.
Absolutely amazing. Loved the tutorial and sat through it for the entire 58 min. Thank you so much for being so informative and I watched this the day before my SQL interview test having no knowledge at all about SQL. Luckily it got me through!!! , Thanks a lot and keep up the amazing work.
Hi Sagar, wow thanks for the amazing Feedback. I didnt dream that my Video could have such an Impact for somebody. Congrats for the new Job 😊
Well done dude! Solved it like a master!
Hats off to you sir.. Amazing solution..
This is really impressive!! Detailed and informative
Hi Geogrey,
thanks for your support and kind words!
To be honest i actually felt why this video is 1 hour and i initially not interested and thinking like skip this one problem in hacker rank but the way cross checked with excell and making the queries in chunks and chunks very brilliant respect brother❤
Good for you that u still watched it! Glad you could learn something 😊👍
one of the best explanation ever, Thanks Man.
Thanks Anurag, that is very encouraging and nice to hear :)
All the best
Markus
Very Nicely Explained!! Thank you so much.
You are welcome, im glad you found it helpful!
You should share the code in the Github link... So it will be easy to copy the code. Thank you for detailed explanation.
Hi Mojahid, thats a great idea! So that's exactly what I did. Here you can find this code:
github.com/MarkusFriemann/HackerRank/blob/main/Interviews
loved ..explained well
hi, thanks for sharing this. 1) Why did we use INNER JOIN? 2) Why did we need temporary table, when would we know from the problem, we would need temporary tables? Thankyou!
Thank you for sharing your work, and thought process. The code was clean and understandable too. For the edge case, isn't it better to you AND instead of OR since it only asked to exclude if all 4 sums are 0? I think the current code might not meet the requirements if only one sum is 0 since it exclude the whole contest.
what i learned
1. to working with snow flake schema we need to join with temp tables.
2. WITH clause allows as many tables you want
thanks i go to update my notes
Great Saurabh, keep going!
Best explanation - I was joining all the 5 tables together and getting the same answer as you were earlier. Now I get the reason of why that is the wrong answer, thanks to your video you made for Sidra :D
But I still have a confusion, so are the sums getting multiplied because of a cardinality clause or is it because we cannot join all of these 5 tables together?
Hi S M Hasan, it is because of cardinality clause. In 46:20 every row in total_view table matches every row in total_submit table (all rows have contest_id). that's why the result multiplies
Sir, can you tell me what changes in have to make for mysql ?
You are awesome sir !! :)
You too!
Best explaination❤️
Thank u!
Hello sir, why are we not joining all five tables together, is it possible to do it that way? Btw, thank you for this solution.
I have a doubt at 24.01, why we were not able to join Contest with other tables while we were successful in joining 3 tables using With clause? Please explain
Hi Rahil. As Han pointed out correctly in the pinned comment: I joined Contests
@@thecodingmentor7701 Thanks, your videos helps in understanding how we should think bit by bit
when we get a problem.
Thanks
Thanks for watching!
Why did we use the Submission_Stats table before View_Stats table
Apart from with statement, how else could one achieve the joining
You can also achieve the joining via subquery. It is more concise. With statement is easier to digest from my opinion. Here is an example for solving it with subquery:
www.hackerrank.com/challenges/interviews/forum/comments/349204
I am sure about the people who have disliked it cannot explain better than you.
Hi Neha, thanks for your comment. Im very happy about all the people that do like the explanations. They will always be people that dislike and that is also okay, thats just a part of youtube :)
@@thecodingmentor7701 Believe me you are fabulous.I am from India and highly appreciate your efforts to teach us in your best way.I am looking forward to connecting with you.
@MM Please explain (if possible) why the previous solution with 6000 total submissions is incorrect ? I mean why it was not a correct solution? what was the reason
P.S: thank you for explaining how you debugged the issue, it was very helpful
Basically first we need to take one test data point
Then check that data point at each stage to see how data is flowing
I didn't know this technique before, thank you
Also can we use multiple joins within a CTE in Mysql
Hi Sidrah
First: Thanks for your question! I made a small video as explanation for you why the total submissions in 46:20 were wrong: th-cam.com/video/TEPKmOmzess/w-d-xo.html
Second: Yes! You can also use with statement in MySQL. See here an explanation: dev.mysql.com/doc/refman/8.0/en/with.html
Greetings
Markus
I have this below while process in run time. could you pls help to solve this one.
Error (stderr)
ERROR 1064 (42000) at line 1: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'total_submit as (
SELECT
coll.contest_id,
SUM(ss.to' at line 1
Hi Pradeep, please write me at Yourquestion@markusfriemann.com so I can help you out
@@thecodingmentor7701 had the same error and sent you a message, please reply
@@olanikedurojaiye2027 This Trick will solve your problems: th-cam.com/video/-0C-yZ8Cee4/w-d-xo.html
Hi, when I tried the code it gave an error, i think someone below complained about same error, I have also reached out to you through an email. Hoping for a reply
Hi Olanike, thanks for your question. Just for you I made another video. This will solve your Problem: th-cam.com/video/-0C-yZ8Cee4/w-d-xo.html
Kindly make a video on that challenge question of hackerrank
Yes, it will come, dont worry! :)
Here is the challenges Problem video as you requested: th-cam.com/video/00yN5ERtxbI/w-d-xo.html&ab_channel=TheCodingMentor
Hii how can I connect you
Hi Logam,
You can write me at: yourquestion@markusfriemann.com
Looking forward to hear from you!
IS there any way i can get this code of yours plz reply
Hi Anurag, Yes! Please find this code here: github.com/MarkusFriemann/HackerRank/blob/main/Interviews
clean as a whistle
Thanks! I try not to make it "too" clean, so you guys can understand the thought process Im going through :)
Hi i have a test in few days so and so company on SQL hacker rank platform any help or suggestions can i get from you...
Hi pranay
Just keep on trying to solve problems for example on HackerRank.
If you have questions you can contact me at: yourquestion@markusfriemann.com
Unable to connect
Hi Logam, how can I help you with this? Greetings
Tutorial sql leetcode challange
Great Idea D P, coming up in the future!