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!
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
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 :)
Hi Ankit, Please find my solution for the above problem. I know the solution is a bit lengthy but i was able to solve the question without checking the solution. All thanks to you. with cte as ( select *, count(submission_id) over(partition by hacker_id order by submission_date rows between unbounded preceding and current row) as total_submissions, count(submission_id) over(partition by hacker_id, submission_date) as total_submissions_by_date from submissions) ,cte2 as ( select submission_date,hacker_id, total_submissions, total_submissions_by_date, case when total_submissions>=day(submission_date) then hacker_id else null end as everyday_submission, row_number() over(partition by submission_date order by total_submissions_by_date desc, hacker_id) as r_no from cte) select submission_date, count(distinct everyday_submission) as unique_cnt, max(case when r_no=1 then hacker_id else null end) as hacker_id from cte2 group by submission_date
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
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
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
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
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
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?
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?
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
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;
Please like the video if you like the solution. It will motivate me to make more challenging videos.
About to 100k 😊.
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!
Great to hear!
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
Sure 😊
Please make video on this one sir..
@@ankitbansal6 Same request from my side as well
Sir, the way you approach the problem is incredible.
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
Haha 😄 I understand
Sir, you are god of SQL. I am amazed the way you approached this problem
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 :)
Excellent
Sir, what an explanation...
you are inspiration of many students.❤
It's my pleasure
Hi Ankit,
Please find my solution for the above problem. I know the solution is a bit lengthy but i was able to solve the question without checking the solution. All thanks to you.
with cte as (
select *, count(submission_id) over(partition by hacker_id order by submission_date rows between unbounded preceding and current row) as total_submissions,
count(submission_id) over(partition by hacker_id, submission_date) as total_submissions_by_date
from submissions)
,cte2 as (
select submission_date,hacker_id, total_submissions, total_submissions_by_date,
case when total_submissions>=day(submission_date) then hacker_id else null end as everyday_submission,
row_number() over(partition by submission_date order by total_submissions_by_date desc, hacker_id) as r_no
from cte)
select submission_date,
count(distinct everyday_submission) as unique_cnt,
max(case when r_no=1 then hacker_id else null end) as hacker_id
from cte2
group by submission_date
Breakdown of the problem is superb.
Thank you 🙏
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
Ankit Bansal Sir, We are Grateful for Your Videos
🙏🙏
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!!!
It's my pleasure🙏
Sir please make More videos on SQL STORED PROCEDURES AND CTE AND VIEWS PROBLEM'S and some extra classes on this Please
Sure
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
ANKIT BANSAL YOU DESERVE BHARATH RATNA AWARD REALLY❤❤❤
Haha 🙏🙏
❤
Super cool question.
what a thriller experience. bow down!!!
🙏
Very well explained
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
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
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
Need to learn on bi tool along with SQL. Tableau or power bi.
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?
The question has nothing to do with the score
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?
Will be uploaded soon in a month
@@ankitbansal6 got it... thanks for the quick reply Sir
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
It is because this hacker is posting something everyday
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
Wow, 🤯
Hi Ankit, can you suggest a basic SQL course for beginners
www.namastesql.com/course-detail/think-sql-go-from-zero-to-hero-english
🤯
It will improve problem solving, so there will 15 days sql series?
The problem name is "15 days of learning SQL" on hacker rank 😊
@@ankitbansal6
It is not that hard problem as it appears!!
Try to solve it 😊
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;
Congratulations @ankitbansal6 for achieving 100K subscribers on You Tube. Really you are deserving and way more to go.