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 🙏

great , I am working on SQL for 10 years but unaware of sol2 logic...Thanks for the precious share

Another interesting concept added to goldmine

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);

Keep coming up with awesome content ! Looking forward to seeing Python questions too for data profiles like you come up with the SQL ones

Great content and explanation as Always 👍

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

Awesome approach. U have dissected it so nicely. In love with your content

Amazing amazing ❤❤❤

Superb explanation 👌 👏 👍

Awesome..🤜

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.

Very well explained👏

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

If you cast the "percent_inc" column as decimal(10,1) then the final result looks exactly same as the hackerrank's output !

Interesting concept Ankit, You are a blessing for the people like us who are preparing for data science domain jobs. God bless you

Method 2 was fentastic

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?

Nice Explanation. Could you please upload some videos about stored procedures if possible.

Instead month number can it be sorted as per month name?

Please provide the dataset as well

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.

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!

Thanks for the great content, loved your explanation! Are there any drawbacks in using the method - 2 advanced aggregation?

@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

Thanks for the explanation. Can you update the complete dataset in the description?

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

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

brother iam not able to find problem code in discription

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.

Hi Ankit , Please give us the schema as well for the Questions. So that we can Solve it.

@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.

No insert script

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!

@Ankit--- can you please share the data set, thanks

what if we would have used "Rows between Unbounded preceding and current row" here.🤔

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.

what if we have multiple year data, here we assumed that the data is of same year.

Please attach DDL

I have another meesho sql question, Can you solve it

Data ya pdf bhi provide Kia karo plj

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