15 Days of Learning SQL | Advanced SQL for Data Analytics

No video

15 Days of Learning SQL | Advanced SQL for Data Analytics

Рет қаралды 21,052

Күн бұрын

15 days of learning SQL
In this video we will deep dive on a problem called "15 days of learning SQL" from hacker rank. This is a challenging SQL problem to solve.
Here is the script:
CREATE TABLE Submissions (
submission_date DATE,
submission_id INT PRIMARY KEY,
hacker_id INT,
score INT
);
INSERT INTO Submissions (submission_date, submission_id, hacker_id, score) VALUES
('2016-03-01', 8494, 20703, 0),
('2016-03-01', 22403, 53473, 15),
('2016-03-01', 23965, 79722, 60),
('2016-03-01', 30173, 36396, 70),
('2016-03-02', 34928, 20703, 0),
('2016-03-02', 38740, 15758, 60),
('2016-03-02', 42769, 79722, 25),
('2016-03-02', 44364, 79722, 60),
('2016-03-03', 45440, 20703, 0),
('2016-03-03', 49050, 36396, 70),
('2016-03-03', 50273, 79722, 5),
('2016-03-04', 50344, 20703, 0),
('2016-03-04', 51360, 44065, 90),
('2016-03-04', 54404, 53473, 65),
('2016-03-04', 61533, 79722, 15),
('2016-03-05', 72852, 20703, 0),
('2016-03-05', 74546, 38289, 0),
('2016-03-05', 76487, 62529, 0),
('2016-03-05', 82439, 36396, 10),
('2016-03-05', 90006, 36396, 40),
('2016-03-06', 90404, 20703, 0);
Zero to hero(Advance) SQL Aggregation:
• All About SQL Aggregat...
Most Asked Join Based Interview Question:
• Most Asked SQL JOIN ba...
Solving 4 Trick SQL problems:
• Solving 4 Tricky SQL P...
Data Analyst Spotify Case Study:
• Data Analyst Spotify C...
Top 10 SQL interview Questions:
• Top 10 SQL interview Q...
Interview Question based on FULL OUTER JOIN:
• SQL Interview Question...
Playlist to master SQL :
• Complex SQL Questions ...
Rank, Dense_Rank and Row_Number:
• RANK, DENSE_RANK, ROW_...
#sql #dataanalytics

Пікірлер: 68

@ankitbansal6 3 ай бұрын

Please like the video if you like the solution. It will motivate me to make more challenging videos.

@ANKITSINGH-ti9ib 3 ай бұрын

About to 100k 😊.

@snehakulkarni1870 3 ай бұрын

The way you approach the problem is incredibly helpful and has significantly improved my problem-solving skills. Thank you for sharing your expertise and making complex concepts easier to understand!

@ankitbansal6 3 ай бұрын

Great to hear!

@shamik2023 3 ай бұрын

Hi Ankit Sir, You are game changer for SQL for many ie SQL Maha Guru. Requesting you to make some SQL performance optimisation videos with use cases how to optimise long running queries with /without ExPlan etc , when to add indexes as part of optimisation,etc

@ankitbansal6 3 ай бұрын

Sure 😊

@vaibhav2347 3 ай бұрын

Please make video on this one sir..

@amittripathi1920 3 ай бұрын

@@ankitbansal6 Same request from my side as well

@addhyasumitra90 Ай бұрын

Mind blowing!! initially when i listen the problem statement ,i thought i couldn't thought i would be able to understand the solution itself. but the way we break it down is truely awesome :)

@ankitbansal6 Ай бұрын

Excellent

@Vaibha293 3 ай бұрын

Sir, the way you approach the problem is incredible.

@Savenature635 10 күн бұрын

Sir, you are god of SQL. I am amazed the way you approached this problem

@aahsankhan4502 3 ай бұрын

Sir, what an explanation... you are inspiration of many students.❤

@ankitbansal6 3 ай бұрын

It's my pleasure

@Datapassenger_prashant 3 ай бұрын

Seriously this was the most frustrating question.. I tried everything but was not able to solve.. But the way you solved it.. was incredibly great

@ankitbansal6 3 ай бұрын

Haha 😄 I understand

@deepeshmatkati3058 3 ай бұрын

Thanks Ankit for taking so much of effort on making such videos.. the way you break such a complex problem into smaller chunk is really Incredible!!!

@ankitbansal6 3 ай бұрын

It's my pleasure🙏

@florincopaci6821 3 ай бұрын

Hello My solution in Sql Server: with flo as ( select submission_date, hacker_id, count(*) over (partition by hacker_id order by submission_date ) as cnt, row_number() over (partition by submission_date order by count(*) desc, hacker_id asc) as rnk from submissions group by submission_date, hacker_id ) select submission_date, count(distinct hacker_id) as unique_cnt , (select hacker_id from flo where f.submission_date=submission_date and rnk=1)as hacker_id from flo f where cnt = day ( submission_date) group by submission_date order by 1 Hope it helps

@nipunshetty9640 3 ай бұрын

Sir please make More videos on SQL STORED PROCEDURES AND CTE AND VIEWS PROBLEM'S and some extra classes on this Please

@ankitbansal6 3 ай бұрын

Sure

@sowmya6471 3 ай бұрын

Breakdown of the problem is superb.

@ankitbansal6 3 ай бұрын

Thank you 🙏

@pavanmadamset 3 ай бұрын

Ankit Bansal Sir, We are Grateful for Your Videos

@ankitbansal6 3 ай бұрын

🙏🙏

@nipunshetty9640 3 ай бұрын

ANKIT BANSAL YOU DESERVE BHARATH RATNA AWARD REALLY❤❤❤

@ankitbansal6 3 ай бұрын

Haha 🙏🙏

@sanocycles6642 3 ай бұрын

❤

@shwetasaini6892 2 ай бұрын

I solved it this way. Your solution looks better than mine with a as( select submission_date, count(distinct hacker_id) as count_dt from( select *, case when submission_date = min(submission_date) over(order by submission_date) then 1 else datediff(day,previous_submission_date,submission_date) end as date_diff from (select submission_date, hacker_id, lag(submission_date) over(partition by hacker_id order by submission_date) as previous_submission_date from submissions ) as x ) as y where date_diff = 1 group by y.submission_date), b as( select submission_date, hacker_id from( select *, rank() over(partition by submission_date order by count_hackers desc, hacker_id asc) as rn from( select submission_date, hacker_id, count(hacker_id) as count_hackers from submissions group by submission_date, hacker_id ) as x ) as y where rn = 1) select a.submission_date, a.count_dt as unique_count, b.hacker_id from a join b on a.submission_date = b.submission_date

@Ashu23200 3 ай бұрын

what a thriller experience. bow down!!!

@ankitbansal6 3 ай бұрын

🙏

@HarshitSingh-lq9yp 2 ай бұрын

Super cool question.

@rajikaursandhu2569 3 ай бұрын

Very well explained

@chandravideo 3 ай бұрын

I dont understand on 5th day why are we taking a hacker which is inconsistent. hacker 2070 should come in output explanation as per question logic

@chandravideo 3 ай бұрын

It is because this hacker is posting something everyday

@harshitkesarwani1750 2 ай бұрын

we are only taking the count of the submission of the person who is consistent and secondly, the hacker_id of another person with maximum number of submissions

@user-dw4zx2rn9v 3 ай бұрын

with cte as ( select submission_date, hacker_id, count(*) as total_submission ,dense_rank() over (order by submission_date) as grp from submissions group by submission_date, hacker_id ) ,cte2 as ( select * , count(*) over (partition by hacker_id order by submission_date) as cnt_till_date, case when grp = count(*) over (partition by hacker_id order by submission_date) then 1 else 0 end as unique_cnt from cte order by submission_date ) ,cte3 as ( select submission_date, total_unique_cnt, hacker_id from (select *, sum(unique_cnt) over (partition by submission_date) as total_unique_cnt, rank() over (partition by submission_date order by total_submission desc, hacker_id) as rnk from cte2 ) as x where rnk = 1 ) select * from cte3

@prateekbakaje7764 3 ай бұрын

Hi Ankit sir, To switch job sql is enough or need to learn other skills. If we learn other skills also compnies will ask for hands on experience. Could you please answer how to tackle this. Also please share your IT journey with us. Thanks

@ankitbansal6 3 ай бұрын

Need to learn on bi tool along with SQL. Tableau or power bi.

@wolf8605 3 ай бұрын

Hello Sir, I have bought your 100 Days of SQL course and I am having few doubts here and there. I am wondering are there any videos available in your site regarding explanation of those questions?

@ankitbansal6 3 ай бұрын

Will be uploaded soon in a month

@wolf8605 3 ай бұрын

@@ankitbansal6 got it... thanks for the quick reply Sir

@NRLakshmi-yc4tk 3 ай бұрын

Ac to the Question, incase of more than 1 hacker has done max submisisions, then lower hacker id is to be taken, (lowest hacker id )which not according to the score?

@florincopaci6821 3 ай бұрын

The question has nothing to do with the score

@priyankagaikwad60 3 ай бұрын

It will improve problem solving, so there will 15 days sql series?

@ankitbansal6 3 ай бұрын

The problem name is "15 days of learning SQL" on hacker rank 😊

@macx8360 2 ай бұрын

@@ankitbansal6

@nobabsheraj7911 2 ай бұрын

with cte as ( select submission_date,hacker_id,count(*) as no_of_Submissions, DENSE_RANK() over (order by submission_date) as Day_Number from Submissions group by submission_date,hacker_id ), cte2 as( select * ,count(*) over(partition by hacker_id order by submission_date) as till_date_Submisssion, case when Day_Number = count(*) over(partition by hacker_id order by submission_date) then 1 else 0 end as Unique_flag from cte ), cte3 as ( select * ,SUM(Unique_flag) over (partition by submission_date ) as unique_count, ROW_NUMBER() over (partition by submission_date order by no_of_Submissions desc, hacker_id ) as rn from cte2 ) select submission_date,unique_count,hacker_id from cte3 where rn=1 order by submission_date,hacker_id

@srikrishna1981 3 ай бұрын

Hi Ankit, can you suggest a basic SQL course for beginners

@ankitbansal6 3 ай бұрын

www.namastesql.com/course-detail/think-sql-go-from-zero-to-hero-english

@anilkumark3573 3 ай бұрын

Wow, 🤯

@nirmalpatel3430 Ай бұрын

It is not that hard problem as it appears!!

@ankitbansal6 Ай бұрын

Try to solve it 😊

@sanjoy8167 3 ай бұрын

Congratulations @ankitbansal6 for achieving 100K subscribers on You Tube. Really you are deserving and way more to go.

@akankshamerupula0607 3 ай бұрын

My solution in MySQL: with cte1 as (Select submission_date,hacker_id, day(submission_date) as d, dense_rank()over(partition by hacker_id order by submission_date) dr from Submissions order by submission_date), cte2 as( select submission_date,hacker_id, row_number()over(partition by submission_date order by count(hacker_id) desc,hacker_id asc) as rnk from Submissions group by submission_date,hacker_id ) select cte1.submission_date,count(distinct(cte1.hacker_id)) as unique_count, cte2.hacker_id from cte1 join cte2 on cte1.submission_date=cte2.submission_date and rnk=1 where d=dr group by cte1.submission_date,cte2.hacker_id;