Bhai tum bohat mast kam karta hai. Kitna bhi difficult problem ho tum kitna easy karke solve kardete ho. Very well explained. And today i learn new thing which is equi join . Thanks a lot sir
You can use this dummy data for solving the question: CREATE TABLE covid_cases ( record_date DATE PRIMARY KEY, cases_count INT ); INSERT INTO covid_cases (record_date, cases_count) VALUES ('2021-01-01',66),('2021-01-02',41),('2021-01-03',54),('2021-01-04',68),('2021-01-05',16),('2021-01-06',90),('2021-01-07',34),('2021-01-08',84),('2021-01-09',71),('2021-01-10',14),('2021-01-11',48),('2021-01-12',72),('2021-01-13',55), ('2021-02-01',38),('2021-02-02',57),('2021-02-03',42),('2021-02-04',61),('2021-02-05',25),('2021-02-06',78),('2021-02-07',33),('2021-02-08',93),('2021-02-09',62),('2021-02-10',15),('2021-02-11',52),('2021-02-12',76),('2021-02-13',45), ('2021-03-01',27),('2021-03-02',47),('2021-03-03',36),('2021-03-04',64),('2021-03-05',29),('2021-03-06',81),('2021-03-07',32),('2021-03-08',89),('2021-03-09',63),('2021-03-10',19),('2021-03-11',53),('2021-03-12',78),('2021-03-13',49), ('2021-04-01',39),('2021-04-02',58),('2021-04-03',44),('2021-04-04',65),('2021-04-05',30),('2021-04-06',87),('2021-04-07',37),('2021-04-08',95),('2021-04-09',60),('2021-04-10',13),('2021-04-11',50),('2021-04-12',74),('2021-04-13',46), ('2021-05-01',28),('2021-05-02',49),('2021-05-03',35),('2021-05-04',67),('2021-05-05',26),('2021-05-06',82),('2021-05-07',31),('2021-05-08',92),('2021-05-09',61),('2021-05-10',18),('2021-05-11',54),('2021-05-12',79),('2021-05-13',51), ('2021-06-01',40),('2021-06-02',59),('2021-06-03',43),('2021-06-04',66),('2021-06-05',27),('2021-06-06',85),('2021-06-07',38),('2021-06-08',94),('2021-06-09',64),('2021-06-10',17),('2021-06-11',55),('2021-06-12',77),('2021-06-13',48), ('2021-07-01',34),('2021-07-02',50),('2021-07-03',37),('2021-07-04',69),('2021-07-05',32),('2021-07-06',80),('2021-07-07',33),('2021-07-08',88),('2021-07-09',57),('2021-07-10',21),('2021-07-11',56),('2021-07-12',73),('2021-07-13',42), ('2021-08-01',41),('2021-08-02',53),('2021-08-03',39),('2021-08-04',62),('2021-08-05',23),('2021-08-06',83),('2021-08-07',29),('2021-08-08',91),('2021-08-09',59),('2021-08-10',22),('2021-08-11',51),('2021-08-12',75),('2021-08-13',44), ('2021-09-01',36),('2021-09-02',45),('2021-09-03',40),('2021-09-04',68),('2021-09-05',28),('2021-09-06',84),('2021-09-07',30),('2021-09-08',90),('2021-09-09',61),('2021-09-10',20),('2021-09-11',52),('2021-09-12',71),('2021-09-13',43), ('2021-10-01',46),('2021-10-02',58),('2021-10-03',41),('2021-10-04',63),('2021-10-05',24),('2021-10-06',82),('2021-10-07',34),('2021-10-08',86),('2021-10-09',56),('2021-10-10',14),('2021-10-11',57),('2021-10-12',70),('2021-10-13',47), ('2021-11-01',31),('2021-11-02',44),('2021-11-03',38),('2021-11-04',67),('2021-11-05',22),('2021-11-06',79),('2021-11-07',32),('2021-11-08',94),('2021-11-09',60),('2021-11-10',15),('2021-11-11',54),('2021-11-12',73),('2021-11-13',46), ('2021-12-01',29),('2021-12-02',50),('2021-12-03',42),('2021-12-04',65),('2021-12-05',25),('2021-12-06',83),('2021-12-07',30),('2021-12-08',93),('2021-12-09',58),('2021-12-10',19),('2021-12-11',52),('2021-12-12',75),('2021-12-13',48);
with cte as ( select month(record_date) as month, sum(cases_count) as total from covid_cases group by day(record_date) ) , cte2 as ( select month, total,sum(total) over(order by month rows between unbounded preceding and 1 preceding) as rnk from cte ) select month,total, (0.1*total/rnk ) from cte2
I did using solution 2 on my own! with cte as (select extract(month from record_date) as mn, sum(cases_count) as cases from covid_cases GROUP BY mn ORDER BY mn) , cte2 as( Select * , (sum(cases) OVER(rows between unbounded preceding and current row)-cases) as previous_case from cte) select mn, (CASE WHEN previous_case>0 THEN ROUND((cases*100.0/previous_case),1) ELSE 0 END) AS percent_inc from cte2
Hi Ankit! Thanks for the content My approach WITH month_cases AS( SELECT (month(record_date)) AS month_no, sum(covid_cases) as month_cases FROM covid_data GROUP BY month(record_date)) SELECT a.month_no,a.month_cases AS curr_month_cases, sum((b.month_cases)) AS prior_month_cases, a.month_cases*100.0/sum((b.month_cases)) AS mom_increase FROM month_cases a left join month_cases b ON a.month_no>b.month_no GROUP BY a.month_no,a.month_cases
Thank you Sir…I have attended this exam…even chatgpt didn’t helped me to solve this question. You are great!! Please can you make one separate video on advanced aggregation and its use cases?
Mysql solution: with cte as ( select month(record_date) as mth , sum(cases_count) as Total_cases from covid_cases group by month(record_date) ) , cte2 as ( select mth, total_cases, sum(total_cases) over (order by mth rows between unbounded preceding and current row) as cul_sum, lag(total_cases,1,0) over (order by mth) as prev_cases, (sum(total_cases) over (order by mth rows between unbounded preceding and current row)) - Total_cases as diff from cte ) select mth, total_cases, diff, Round(100*(total_cases/diff),2) as Percentage_change from cte2
Hey Ankit, amazing explanation! Could you please share the entire dataset in the description box? The video shows you have dataset for all days in the year. However the description box dataset has only 13 rows.
Amazing video! Every day, I try to solve at least one SQL problem from your videos. Thank you for your hard work. It would be fantastic if you could also begin a Python series.
You used the formula "current cases/cumulative cases," but shouldn't we be using "(current month - previous month)/cumulative cases" as this correctly gives the percentage increase?
Hi Ankit, Thanks for sharing such solutions. Here is my solution to this problem: WITH monthly_cases AS ( SELECT MONTH(record_date) AS Month, SUM(cases_count) AS Monthly_Cases FROM daily_cases GROUP BY MONTH(record_date) ), Cumulative_Case AS ( SELECT Month, Monthly_Cases, SUM(Monthly_Cases) OVER (ORDER BY Month) AS Cumulative_Cases FROM monthly_cases ) SELECT Month, Monthly_Cases, Cumulative_Cases, (Monthly_Cases / LAG(Cumulative_Cases) OVER (ORDER BY Month)) * 100 AS Percent_Increase FROM Cumulative_Case; Please let me know if this will work or not. Thank You!
I was asked a similar question yesterday during my 1st technical round for BIE with Amazon and I failed miserably 😭😭😭😭 I wish this was uploaded couple of days ago!
Ye IT wale kuch bbi puchte hai jaise in sabko lagta hai ki hamara dimaag Einstein jaisa hai,,,, khud interviewer ko kych nhi aata aur hamse Einstein jaisi umeed rekhte hai
@ankitbansal6 sir please paste the script in the description or comments or provide a link.. so that it would be easy for the learners to practice. thank you
can you share the queries, create.. insert to try... thanks! also i have used the agg on sum like you did but have not used the unbound condition.. still it is working.. any thoughts..
@ankitbansal great leaning from you may I get the dataset of this question or you can please let me know where can I get the dataset of this question so that I can practice this.
select record_month, monthly_cases, cumm_prior_month, monthly_cases*1.0 / cumm_prior_month as perc_increase from ( select record_month, monthly_cases, sum(monthly_cases) over(order by record_month rows between 1 preceeding and current row) as cumm_prior_month from ( select month(record_date) as record_month, sum(cases_count) as monthly_cases from covid_cases group by month(record_date) ) )
Hi, I tried solving this question today in hackerrank test by meesho but couldn't get the result in ms sql server . can you please check?? by the first method of non equi join.
Yes today also i tried solving the same question in Meesho test with the second approach, still in hackerrank , although the output matched, it did not passed the test cases just because of the headers of "month","percent_increase".
Run the below script to create table and insert sample data. CREATE TABLE covid_cases ( record_date DATE PRIMARY KEY, cases_count INT ); DECLARE @end_date date; DECLARE @loop_date date; DECLARE @num INT; set @end_date = '2021-12-31' set @loop_date = '2021-01-01' WHILE @loop_date
My solution in MS SQL Server: with cte as( select year(record_date) year, format(record_date, 'MMM') month, Datepart(month, record_date) month_no, sum(cases_count) total_cases from covid_cases group by year(record_date), format(record_date, 'MMM'), Datepart(month, record_date)), cte2 as( select year, month, total_cases, coalesce(sum(total_cases) over(order by year, month_no rows between unbounded preceding and 1 preceding), 0) as rolling_sum from cte) select concat_ws('-', year, month) month, total_cases, IIF(rolling_sum = 0, 0, cast(100*total_cases/cast(rolling_sum as float) as decimal(10,1))) as percentage from cte2
I hope non equi join is clear now to everyone. Let me know which solution you think should be mentos zindagi 😎
Dont forget to like the videos 🙏
Thank you ankit bhai
Bhai tum bohat mast kam karta hai. Kitna bhi difficult problem ho tum kitna easy karke solve kardete ho. Very well explained. And today i learn new thing which is equi join . Thanks a lot sir
great , I am working on SQL for 10 years but unaware of sol2 logic...Thanks for the precious share
Glad it was helpful!
You can use this dummy data for solving the question: CREATE TABLE covid_cases (
record_date DATE PRIMARY KEY,
cases_count INT
);
INSERT INTO covid_cases (record_date, cases_count) VALUES
('2021-01-01',66),('2021-01-02',41),('2021-01-03',54),('2021-01-04',68),('2021-01-05',16),('2021-01-06',90),('2021-01-07',34),('2021-01-08',84),('2021-01-09',71),('2021-01-10',14),('2021-01-11',48),('2021-01-12',72),('2021-01-13',55),
('2021-02-01',38),('2021-02-02',57),('2021-02-03',42),('2021-02-04',61),('2021-02-05',25),('2021-02-06',78),('2021-02-07',33),('2021-02-08',93),('2021-02-09',62),('2021-02-10',15),('2021-02-11',52),('2021-02-12',76),('2021-02-13',45),
('2021-03-01',27),('2021-03-02',47),('2021-03-03',36),('2021-03-04',64),('2021-03-05',29),('2021-03-06',81),('2021-03-07',32),('2021-03-08',89),('2021-03-09',63),('2021-03-10',19),('2021-03-11',53),('2021-03-12',78),('2021-03-13',49),
('2021-04-01',39),('2021-04-02',58),('2021-04-03',44),('2021-04-04',65),('2021-04-05',30),('2021-04-06',87),('2021-04-07',37),('2021-04-08',95),('2021-04-09',60),('2021-04-10',13),('2021-04-11',50),('2021-04-12',74),('2021-04-13',46),
('2021-05-01',28),('2021-05-02',49),('2021-05-03',35),('2021-05-04',67),('2021-05-05',26),('2021-05-06',82),('2021-05-07',31),('2021-05-08',92),('2021-05-09',61),('2021-05-10',18),('2021-05-11',54),('2021-05-12',79),('2021-05-13',51),
('2021-06-01',40),('2021-06-02',59),('2021-06-03',43),('2021-06-04',66),('2021-06-05',27),('2021-06-06',85),('2021-06-07',38),('2021-06-08',94),('2021-06-09',64),('2021-06-10',17),('2021-06-11',55),('2021-06-12',77),('2021-06-13',48),
('2021-07-01',34),('2021-07-02',50),('2021-07-03',37),('2021-07-04',69),('2021-07-05',32),('2021-07-06',80),('2021-07-07',33),('2021-07-08',88),('2021-07-09',57),('2021-07-10',21),('2021-07-11',56),('2021-07-12',73),('2021-07-13',42),
('2021-08-01',41),('2021-08-02',53),('2021-08-03',39),('2021-08-04',62),('2021-08-05',23),('2021-08-06',83),('2021-08-07',29),('2021-08-08',91),('2021-08-09',59),('2021-08-10',22),('2021-08-11',51),('2021-08-12',75),('2021-08-13',44),
('2021-09-01',36),('2021-09-02',45),('2021-09-03',40),('2021-09-04',68),('2021-09-05',28),('2021-09-06',84),('2021-09-07',30),('2021-09-08',90),('2021-09-09',61),('2021-09-10',20),('2021-09-11',52),('2021-09-12',71),('2021-09-13',43),
('2021-10-01',46),('2021-10-02',58),('2021-10-03',41),('2021-10-04',63),('2021-10-05',24),('2021-10-06',82),('2021-10-07',34),('2021-10-08',86),('2021-10-09',56),('2021-10-10',14),('2021-10-11',57),('2021-10-12',70),('2021-10-13',47),
('2021-11-01',31),('2021-11-02',44),('2021-11-03',38),('2021-11-04',67),('2021-11-05',22),('2021-11-06',79),('2021-11-07',32),('2021-11-08',94),('2021-11-09',60),('2021-11-10',15),('2021-11-11',54),('2021-11-12',73),('2021-11-13',46),
('2021-12-01',29),('2021-12-02',50),('2021-12-03',42),('2021-12-04',65),('2021-12-05',25),('2021-12-06',83),('2021-12-07',30),('2021-12-08',93),('2021-12-09',58),('2021-12-10',19),('2021-12-11',52),('2021-12-12',75),('2021-12-13',48);
Thank you So much For This
Thanks for data set
thank you bro
with cte as (
select month(record_date) as month, sum(cases_count) as total from covid_cases
group by day(record_date) ) ,
cte2 as (
select month, total,sum(total) over(order by month rows between unbounded preceding and 1 preceding) as rnk from cte )
select month,total, (0.1*total/rnk ) from cte2
I am in love with your SQL videos thankyou so much sir
I did using solution 2 on my own!
with cte as
(select extract(month from record_date) as mn, sum(cases_count) as cases from covid_cases
GROUP BY mn
ORDER BY mn)
, cte2 as(
Select * , (sum(cases) OVER(rows between unbounded preceding and current row)-cases) as previous_case from cte)
select mn, (CASE WHEN previous_case>0 THEN ROUND((cases*100.0/previous_case),1) ELSE 0 END) AS percent_inc from cte2
where did you get the data?
@@debabratabar2008 ask chatGPT to create, it will do.
Another interesting concept added to goldmine
Keep coming up with awesome content ! Looking forward to seeing Python questions too for data profiles like you come up with the SQL ones
More to come!
Awesome approach. U have dissected it so nicely. In love with your content
Glad to hear that
Relieved I knew the second solution. Feeling more confident about Meesho BA hiring now.
Hi Ankit! Thanks for the content
My approach
WITH month_cases AS(
SELECT (month(record_date)) AS month_no,
sum(covid_cases) as month_cases
FROM covid_data
GROUP BY month(record_date))
SELECT a.month_no,a.month_cases AS curr_month_cases,
sum((b.month_cases)) AS prior_month_cases,
a.month_cases*100.0/sum((b.month_cases)) AS mom_increase
FROM month_cases a
left join month_cases b
ON a.month_no>b.month_no
GROUP BY a.month_no,a.month_cases
Hi Ankit,
I have been following your videos from so long and I am happy to say that I solved it using advanced aggregation in the first go.
Excellent!!
Best question to practice Non-Equi Join, Bhaiya can you please provide dataset, so we can do handson.
Thank you Sir…I have attended this exam…even chatgpt didn’t helped me to solve this question. You are great!! Please can you make one separate video on advanced aggregation and its use cases?
Here you go
th-cam.com/video/5Ighj_2PGV0/w-d-xo.html
Mysql solution: with cte as (
select month(record_date) as mth , sum(cases_count) as Total_cases from covid_cases
group by month(record_date)
)
, cte2 as (
select mth, total_cases, sum(total_cases) over (order by mth rows between unbounded preceding and current row) as cul_sum,
lag(total_cases,1,0) over (order by mth) as prev_cases, (sum(total_cases) over (order by mth rows between unbounded preceding and current row)) - Total_cases as diff from cte
)
select mth, total_cases, diff, Round(100*(total_cases/diff),2) as Percentage_change from cte2
Great content and explanation as Always 👍
Hey Ankit, amazing explanation!
Could you please share the entire dataset in the description box?
The video shows you have dataset for all days in the year. However the description box dataset has only 13 rows.
There is a character limit so it can't be put full. You can create similar data for 365 days yourself
Thanks for acknowledging, Ankit.
Didn’t know about this limit.
@@ankitbansal6 can u share file link in desc?
@@shivukaraguppi6984 you can just use the sample data and generate the 365 days data in Excel by dragging. Data need not be exactly same :)
Interesting concept Ankit, You are a blessing for the people like us who are preparing for data science domain jobs. God bless you
Thanks a ton🙏
Nice Explanation. Could you please upload some videos about stored procedures if possible.
Sure I will
Amazing video! Every day, I try to solve at least one SQL problem from your videos. Thank you for your hard work. It would be fantastic if you could also begin a Python series.
Great suggestion!
Yes Ankit. for python we are not getting deep problem solving analysis like you did for SQL
Yes I second that , kindly do come up with a similar Python series and that will be truly helpful
@@sanrhn for python is it coding or pandas questions ?
@@ankitbansal6 Every question that is important from an interview perspective. The rest I believe can be learned on the job.
If you cast the "percent_inc" column as decimal(10,1) then the final result looks exactly same as the hackerrank's output !
You used the formula "current cases/cumulative cases," but shouldn't we be using "(current month - previous month)/cumulative cases" as this correctly gives the percentage increase?
Hi Ankit,
Thanks for sharing such solutions.
Here is my solution to this problem:
WITH monthly_cases AS (
SELECT
MONTH(record_date) AS Month,
SUM(cases_count) AS Monthly_Cases
FROM
daily_cases
GROUP BY
MONTH(record_date)
),
Cumulative_Case AS (
SELECT
Month,
Monthly_Cases,
SUM(Monthly_Cases) OVER (ORDER BY Month) AS Cumulative_Cases
FROM
monthly_cases
)
SELECT
Month,
Monthly_Cases,
Cumulative_Cases,
(Monthly_Cases / LAG(Cumulative_Cases) OVER (ORDER BY Month)) * 100 AS Percent_Increase
FROM
Cumulative_Case;
Please let me know if this will work or not. Thank You!
Superb explanation 👌 👏 👍
Thank you 🙂
I was asked a similar question yesterday during my 1st technical round for BIE with Amazon and I failed miserably 😭😭😭😭
I wish this was uploaded couple of days ago!
Better luck next time 😊
Ye IT wale kuch bbi puchte hai jaise in sabko lagta hai ki hamara dimaag Einstein jaisa hai,,,, khud interviewer ko kych nhi aata aur hamse Einstein jaisi umeed rekhte hai
Method 2 was fentastic
Very well explained👏
Thank you 🙂
Thanks for the explanation. Can you update the complete dataset in the description?
There is a character limit. You can create some data for 365 days.
Thanks for the great content, loved your explanation!
Are there any drawbacks in using the method - 2 advanced aggregation?
Nope. It's actually better.
Amazing amazing ❤❤❤
Awesome..🤜
Thank you! Cheers!
can we get the dataset for this?? i couldnt find the full data set
Well explained sir!
Can we use alias name in the group by instead of using the same select function? Would like to get educated on this
Nope . group by runs before select
Hi Ankit , Please give us the schema as well for the Questions. So that we can Solve it.
Description box
Instead month number can it be sorted as per month name?
Join condition should be
@ankitbansal6 sir please paste the script in the description or comments or provide a link.. so that it would be easy for the learners to practice. thank you
can you share the queries, create.. insert to try... thanks!
also i have used the agg on sum like you did but have not used the unbound condition.. still it is working.. any thoughts..
Check description box
Please provide the dataset as well
@ankitbansal great leaning from you may I get the dataset of this question or you can please let me know where can I get the dataset of this question so that I can practice this.
Description box
brother iam not able to find problem code in
discription
select record_month, monthly_cases, cumm_prior_month, monthly_cases*1.0 / cumm_prior_month as perc_increase from (
select record_month, monthly_cases, sum(monthly_cases) over(order by record_month rows between 1 preceeding and current row) as cumm_prior_month from (
select month(record_date) as record_month, sum(cases_count) as monthly_cases from covid_cases group by month(record_date) ) )
Why do we need two ctes when we can solve this by one cte only, use lag(cases_count,1)
Hi, I tried solving this question today in hackerrank test by meesho but couldn't get the result in ms sql server . can you please check?? by the first method of non equi join.
Yes today also i tried solving the same question in Meesho test with the second approach, still in hackerrank , although the output matched, it did not passed the test cases just because of the headers of "month","percent_increase".
what if we would have used "Rows between Unbounded preceding and current row" here.🤔
Then it will be till current month not prior month
@Ankit--- can you please share the data set, thanks
Description box
No insert script
I have another meesho sql question, Can you solve it
Send it to sql.namaste@gmail.com
@@ankitbansal6 Sent Questions
please check
@@Techie-Harry Got it
@@ankitbansal6 will you post video on these question
what if we have multiple year data, here we assumed that the data is of same year.
Then you just need to use the year also in join for the first solution . In the second you need to add year in order by before month
Sir how can I buy your course
www.namastesql.com/course-detail/think-sql-go-from-zero-to-hero-english
Run the below script to create table and insert sample data.
CREATE TABLE covid_cases (
record_date DATE PRIMARY KEY,
cases_count INT
);
DECLARE @end_date date;
DECLARE @loop_date date;
DECLARE @num INT;
set @end_date = '2021-12-31'
set @loop_date = '2021-01-01'
WHILE @loop_date
My solution in MS SQL Server:
with cte as(
select year(record_date) year, format(record_date, 'MMM') month,
Datepart(month, record_date) month_no, sum(cases_count) total_cases from covid_cases
group by year(record_date), format(record_date, 'MMM'), Datepart(month, record_date)),
cte2 as(
select year, month, total_cases, coalesce(sum(total_cases)
over(order by year, month_no rows between unbounded preceding and 1 preceding), 0) as rolling_sum
from cte)
select concat_ws('-', year, month) month, total_cases,
IIF(rolling_sum = 0, 0, cast(100*total_cases/cast(rolling_sum as float) as decimal(10,1))) as percentage
from cte2
can i get this data link?
Description box
Please attach DDL
Description box
@@ankitbansal6 not complete
Data ya pdf bhi provide Kia karo plj
Description box