The Hardest Problem | Hackerrank SQL Solutions | 15 Days of Learning SQL Part 2 of 2

Watch the Solution of the Hardest SQL Problem on Hackerrank.
Find the Code in this video: github.com/MarkusFriemann/Hac...
____________________________________________
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-fr. .
____________________________________________
0:00 Introduction
0:30 Where we left off
01:00 Variables, Tables Variables and WHILE Loop
02:14 Declare First Table Variable consistent hackers
03:38 Declare Variables subdate and remainingdate
04:55 Get Hackers for first day
07:17 Get Hackers for all days with WHILE
13:30 JOIN Hackers with consistent hackers
19:03 Declare second Table Variable count hackers
21:32 Combine Part 1 and Part 2 for final Solution

[@sudeshnasen2731]

Thank you for this 2 part video! The explanations and walkthroughs were very detailed and clear!

[@keyiyu9178]

Thank you so much for detailed explanation, and I have learned quite a lot from you for covering multiple SQL skills.👍

[@zlovebae6679]

I learned a lot from this and I like the way you approach the problem from the beginning. I wish you can do more SQL problem like this. I always approach the problem in the hard way. Also if you can post video about how to approach problem like this will be very useful. Like what's going in your mind and how do you know what tools to use? THANKS AGAIN! From Canada

[@thecodingmentor7701]

Hi Zlove, thanks for your comment. I might do a video like this in the future. I can tell you that practice is the best way to train your brain in solving these problems.

[@caro345mng]

Thank you so much

[@falutaope2800]

Kindly shed a bit of light on this area please.
I was about to text that i don't understand your solution i had to pause and walk my way back via excel to see what really happened. This question really tests one's understanding of the JOIN concept. It's a brilliant solution ngl.

[@amgdhussein7040]

This is by far the simplest solution on this problem comparing to all jebrish answers on hacker rank.
Thank you for establishing the logic first then breaking it down for us
I learned alot.
For while Loop, it had < 15th meaning it won't loop over the 15th which is needed date to be looped on. Can you clarify please?

[@thecodingmentor7701]

Hi Amgd, thanks for the question and for being so active on my videos! That's a great question.
We loop < 15 because right after the loop we use the function DATEADD which adds 1 day to the date. We loop until 14th and then add a day right after to be in the 15th day. That's how we cover all necessary dates.

[@souravbanerjee1692]

Does this solution work for Oracle Pl/SQL ?

[@thecodingmentor7701]

Hi Sourav, my Solutions are for MSSQL. you might have to change it a bit for oracle :)

[@orderedchief]

Hello!, the link to code doen't work

[@thecodingmentor7701]

Hi Juan, thanks for the feedback, its working now!

[@CrustyGiantFishFinger]

I've only just started working on the questions on HackerRank. Here's my SQL Server solution to this one:
;with cte1 as (
Select s.hacker_id
, submission_date
from submissions s
), Recursion1 as (
Select Hacker_id, submission_date
from cte1
where submission_date = '20160301'
union all
Select s.Hacker_id, s.Submission_date
from cte1 c
inner join submissions s on s.submission_date = dateadd(day,1,c.submission_date) and s.hacker_id = c.hacker_id
where s.submission_date between '20160301' and '20160315'
), Recursion2 as (
Select Submission_date, Hacker_ID
from Recursion1
where Submission_date = '20160301'
union all
Select r1.Submission_date, r1.Hacker_ID
from Recursion2 r
inner join Recursion1 r1 on r1.submission_date = dateadd(day,1,r.submission_date) and r.hacker_id = r1.hacker_id
), MinHackerAndName as (
Select dc.Submission_date, dc.[Hacker_SubmissionCount], dc.Hacker_id
,row_number() over (partition by dc.submission_date order by dc.Submission_date asc, dc.Hacker_SubmissionCount desc, dc.Hacker_id asc) [RN]
from (
Select s.Submission_date, s.hacker_id, count(s.Hacker_id)[Hacker_SubmissionCount]
from Submissions s
group by s.submission_date, s.hacker_id) dc
)
Select r.Submission_date, Count(distinct r.Hacker_id) [HCount]
, h.Hacker_id, h.Name
from Recursion2 r
inner join MinHackerAndName mhn on mhn.submission_date = r.submission_date and mhn.rn = 1
inner join Hackers h on h.Hacker_id = mhn.hacker_id
group by r.Submission_date , h.Hacker_id, h.Name
Order by r.Submission_date asc