In this video we will learn how to find running/rolling calculations in SQL. This is very important concept and very frequently used in analytical projects.
Пікірлер: 55
@ianpropst-campbell60282 жыл бұрын
Thank you for your helpful explanation of rolling calculations!
@sumksa Жыл бұрын
Thank you so much. May you have all the success in your life 🙏🙏
@ankitbansal6 Жыл бұрын
Thank you 🙏
@Aarohi_Zara Жыл бұрын
select year(order_date), month(order_date), sum(sales) , sum(sum(sales)) over(order by year(order_date), month(order_date) rows between 2 preceding and current row) , avg(sum(sales)) over(order by year(order_date), month(order_date) rows between 2 preceding and current row) , max(sum(sales)) over(order by year(order_date), month(order_date) rows between 2 preceding and current row) , min(sum(sales)) over(order by year(order_date), month(order_date) rows between 2 preceding and current row) from orders group by year(order_date), month(order_date) order by 1, 2
@kanchidoshi69072 жыл бұрын
Good content. Can you please make more business scenario specific videos?
@ankitbansal62 жыл бұрын
sure
@fozantalat45092 жыл бұрын
Hello Ankit, My name is Fozan I'm from Pakistan , your videos are very helpful please also make assignment questions, so that we can test our understanding.
@ankitbansal62 жыл бұрын
Hi Fozan, Good to know my Pakistan friends are getting some benefit out of my videos. I will do that 🙂
@TienDuong-ck4jf3 ай бұрын
Thanks for your clear instruction :D
@ankitbansal63 ай бұрын
You're welcome!
@DeshaKannada3 ай бұрын
Great 🎉
@mr2262 Жыл бұрын
How to calculate the prior rolling 12 months average (current year = 2023, prior = 2022) -- I tried this: rows 24 preceding and 12 preceding
@abhishekarora12 жыл бұрын
Does proceeding keyword is specific to Microsoft SQL Server or it would work across different RDBMS?
@ankitbansal62 жыл бұрын
It works across most of the databases
@abhishekarora12 жыл бұрын
@@ankitbansal6 thanks for quick reply!
@sudarshanthota44442 жыл бұрын
Nice video 👍
@ankitbansal62 жыл бұрын
Thank you 😊
@mgopinath90805 ай бұрын
Amazing😍
@ankurrunthala Жыл бұрын
Very good explanation 🙂
@ankitbansal6 Жыл бұрын
Thank you 🙂
@utkarshtrivedi99492 жыл бұрын
Also it will be very helpful if you can provide the data with each video to practice it also.
@ankitbansal62 жыл бұрын
Okay
@explorer_baba27502 жыл бұрын
Hi Ankit, how can we find rolling sum without using any windows function . Today, One interviewer has asked me this question. If anyone has any idea can share their thoughts.
@ankitbansal62 жыл бұрын
Can be done using self join
@SunilKumar_674 ай бұрын
I am working on writing a query which will give the number of hours left on starting day and it should gradually decrease towards end day and reach zero. Can you please create a video for this?
@ankitbansal64 ай бұрын
24- hour(sysdate)
@amangupta19592 жыл бұрын
Hi Ankit, Could you please provide data for this tutorial? I practice side by side as I go by the video. Thanks in advance.
@ankitbansal62 жыл бұрын
You can download data from below link: drive.google.com/drive/folders/1Dc81McsB4lp1JUIwssDmmOaw6Z7rBK8T?usp=sharing In below video I have explained how to import the data: kzfaq.info/get/bejne/ja-jg9yK26u6Ypc.html
@amangupta19592 жыл бұрын
@@ankitbansal6 thank you
@sashipanda34052 жыл бұрын
how can we apply the same logic in postgre please help
@sashipanda34052 жыл бұрын
adding to above if there are year,month,weeks column
@mithunnambiar14332 жыл бұрын
Hi Ankit, A request... Could you please provide the schema of this query?
@ankitbansal62 жыл бұрын
You can download superstore data from this link drive.google.com/drive/mobile/folders/1Dc81McsB4lp1JUIwssDmmOaw6Z7rBK8T?usp=sharing
@sachinkumargupta56602 жыл бұрын
Awsome
@utkarshtrivedi99492 жыл бұрын
Please make a video on NTILE, LAG and LEAD function also
@ankitbansal62 жыл бұрын
Sure
@Ajaybchaudhari18 күн бұрын
1:03 & 5:51 what is three sum?
@definitesquare8 күн бұрын
The column rolling_3_sum contains the sum of (that month, prev month and prev prev month)
@harishkanta3711 Жыл бұрын
Ankit bhai what does unbounded preceding and current following give.
@ankitbansal6 Жыл бұрын
Running sum
@harishkanta3711 Жыл бұрын
@@ankitbansal6 thank you
@shobhitsharma2137 Жыл бұрын
how to get dataset
@shreyashchoudhary68272 жыл бұрын
please provide data ,create and insert stmt ,github or here
@ankitbansal62 жыл бұрын
Provided in today's video
@yatinshekhar78711 ай бұрын
9/142
@vishalsonawane.89054 ай бұрын
Please add the data
@vandanaK-mh9zo8 ай бұрын
with cte as ( select datepart(year, order_date) as sales_year, datepart(month, order_date) as sales_month, sum(sales) as sales From cust_orders group by sales_year,sales_month) select *, sum(sales) over (order by sales_year asc, sales_month asc rows between 2 preceding and current row) as rolling_sum, min(sales) over (order by sales_year asc, sales_month asc rows between 2 preceding and current row) as rolling_min, max(sales) over (order by sales_year asc, sales_month asc rows between 2 preceding and current row) as rolling_max, avg(sales) over (order by sales_year asc, sales_month asc rows between 2 preceding and current row) as rolling_avg from cte;