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.
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
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.
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;
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.
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: ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-WIsPyYvEV2c.html
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?