Window Functions - Calculate Running Differences

Another video brought to you by BeardedDev, bringing you tutorials on Business Intelligence, SQL Programming and Data Analysis.
You can now support me on patreon - / beardeddev
If you are new to working with window functions check out this video: • SQL Tutorial - Window ...
In this video I talk about how to calculate a running difference using LAG and Window Functions.
Also covered in this video:
CTE - Common Table Expressions
Aggregate Functions - Average
WITH CTE
AS
(
SELECT
Sales_Customer_Id
, Sales_Date
, Sales_Amount
, LAG(Sales_Amount) OVER(PARTITION BY Sales_Customer_Id ORDER BY Sales_Date) AS PrevValue
, Sales_Amount - LAG(Sales_Amount) OVER(PARTITION BY Sales_Customer_Id ORDER BY Sales_Date) AS RunningDifference
FROM dbo.Sales
)
SELECT
Sales_Customer_Id
, AVG(RunningDifference) AS AverageDifference
FROM CTE
GROUP BY Sales_Customer_Id
ORDER BY AverageDifference DESC;
Please feel free to post comments.

[@oz7075]

What a great series. Thanks a lot.

[@ryanlau1263]

Thanks Dev, super useful!

[@drishtantkaushal]

Thanks a lot @BearededDev for creating this amazing series on Windows Functions. I have found it very useful. I wanted to check if it were possible for you to make the data (or a portion of it) you use to offer a walk-through in these videos available. It will be really helpful to get a hands-on experience see the results for ourselves. Apologies if you already that data available somewhere, I didn't see those in the video descriptions so wanted to reach out if you could point me to the data source if there is one.

[@BeardedDevData]

Hi, thanks for the comment, I have made another video on window Functions that you can follow along with, m.ua-cam.com/video/lBcDSsgp0RU/v-deo.html. My initial thoughts when creating the videos was not to include data because I thought if people can take the tutorial and apply it to their own data then they have learnt how to do it but I’ve gathered the feedback and understand that people like to follow along and have started including data with my more recent videos.

[@drishtantkaushal]

Thanks for circling back @@BeardedDevData! And thank you so much for sharing the link to the "follow along" video for Window Functions!

[@llatyntseva]

Thanks :) Subscribed

[@debeshkumar4697]

Hey dev it would be great if you could explain Pl/sql and joins using some complicated data sets

[@moofik]

Agree!

[@vijayabhaskarnaidukandukur5305]

just subscribed the channel. i have been following the videos. wanted to check if i can get the sql scripts for the tables/data that been used in the video's as i was planning to experiment a bit

[@BeardedDevData]

I don’t have the scripts anymore but you can get Adventure Works databases easily online, this will allow you to practice Window Functions.

[@farisharidi80]

Hi Dev,
Your videos are very nice and makes it simple for me to understand the concept.
There are others statistical window function (first_value) and (last_value). We need a video for these and how can be used in real cases... The benefits of these 2 functions...

[@BeardedDevData]

Hi Faris, thanks for your positive feedback. I will get some videos up on first and last value by the end of the week. I’ve got some more window functions videos planned over the next couple of weeks so keep a look out for those.

[@farisharidi80]

@@BeardedDevData thank for promptly response...

[@olaayorinde6865]

Nice video sir, i just stumbled on your channel. Pls what is the difference between loading data into #temp table and using CTE

[@BeardedDevData]

There are quite a lot of differences to go through but the main ones are; CTEs only have a scope of the query which means they must be declared within the same query they are queried, they are useful for performing basic table operations such as grouping and then querying or deleting duplicates, temp tables are different in that they are within the scope of the session, if you have a table you need to refer to multiple times temp tables are a good choice, you can create indexes on temp tables, an example usage would be if we have a large table and we only need to retrieve a few rows but we want to refer to these rows multiple times in the query then a temp table would be a good choice. This topic would make a good video and I will upload one shortly to cover this question more in depth.

[@olaayorinde6865]

@@BeardedDevData Thank you very much!

[@chiedoziie]

What if you want to find running count for unique customerid and sort by salesdate

[@BeardedDevData]

In that case, you just want a running total, you can use the count function, partition by customerid and sort by sales date, you might want to check out this video: ua-cam.com/video/6S7z2wabJxk/v-deo.html

[@chiedoziie]

@@BeardedDevData yes I thought so too, however it didn’t return for unique customerids

[@BeardedDevData]

Can you tell me a bit more about what you are trying to achieve and what the underlying data looks like?

[@rai_nav]

In this example, every customer had only one transaction per day. What if there are multiple transactions per day, and we want to aggregate the values first and then calculate the difference?

[@BeardedDevData]

That’s a good question, i would recommend using a derived table that does the initial grouping and then when selecting from the result you can apply the window function. You can actually achieve a window function on top of a group by in the same query but using a derived table or CTE makes the code nearer and easier to follow.

[@houstonvanhoy7767]

@@BeardedDevData Neater?

[@nityanandwachche5684]

H BeardedDev,
Can you please add video related to the correlated subquery, SQL interview questions
Thanks

[@BeardedDevData]

Sure, I will get a video up shortly, thanks for the recommendation.