SQL Window Functions: Explained (with examples)

📝 Get my free SQL Cheat Sheets: www.databasestar.com/get-sql-...
🎓 Learn and become confident in SQL: databasestar.mykajabi.com/sql...
Window functions in SQL are a very helpful feature if you want to perform calculations on your data.
But they can be a little tricky to understand.
In this video, you’ll learn what a window function is, why you would use one, and see a couple of examples on an SQL database.
This functionality is available in Oracle, SQL Server, MySQL, and Postgres.
Timestamps:
00:00 What is a window function?
00:47 The sample data and running total
01:39 Window function syntax explained
02:45 Demo of SQL with a running total
05:22 The partition concept
06:25 Adding a “partition by” clause to our query
08:02 Other scenarios for window functions
Links:
My GitHub repository with my SQL scripts: github.com/bbrumm/databasestar
The SQL script used for this video: github.com/bbrumm/databasesta...
Article: SQL Window Functions: www.databasestar.com/sql-wind...

[@eminjs2319]

First of all, thanks for clarification. Second, just to warn some people like me who could potentially be stumped by a little typo: in 6:09, in the presentation, write to the order_id 5 the date also 2022--04-03, and not 2022-03-03, otherwise we would not have a logical grouping. Again, I want to than you again Ben: only after watching this video I truly feel that I grasp the window functions completely. Kudos to your amazing clarification skills.

[@DatabaseStar]

Great, thanks for the correction and advice!

[@andresil8330]

Thanks, Ben! You nailed in the explanation and can show things in a very clear way.

[@DatabaseStar]

Thanks!

[@huffypuff0]

This is honestly such an amazing video. Thank you so much.

[@DatabaseStar]

Thanks! Glad you like the video.

[@alaeeddinehajji]

you have the best SQL playlist, Thank you

[@DatabaseStar]

Thanks!

[@osoriomatucurane9511]

Another Awesome tutorial, crystal clear and concise by the SQL Master! This man is truly a legend! After numerous tutorial, I finally got my head arround the order by argument in the over ( ) clause. Thank you a lot. This is the relative sorting against or over the particular group/window.

[@DatabaseStar]

Thanks! I'm glad it helped you understand.

[@Port0101]

You have done great when it comes to good video creation and Knowledge delivery. Nice! Learned what I needed. Nice!

[@DatabaseStar]

Thanks! I’m glad you liked the video.

[@shirtless32]

Great teacher!!! One of the best I have seen.

[@DatabaseStar]

Thanks a lot!

[@ModernPulse1]

Great explanation. Thanks!

[@DatabaseStar]

You're welcome!

[@nataliataranova7480]

Clear & informative tutorial, thank you!

[@DatabaseStar]

Glad it was helpful!

[@ChiaMIaLALALA]

great video! very helpful! 😊

[@DatabaseStar]

Glad it was helpful!

[@user-ev2th8zb3m]

Really helpful! Thanks!

[@DatabaseStar]

Glad it was helpful!

[@severtone263]

This was very helpful! TY

[@DatabaseStar]

Thanks!

[@samplaying4keeps]

Thank you so much for this. It was very clear and helpful.

[@DatabaseStar]

Glad it was helpful!

[@pranjalsingh9154]

Very helpful, Thanks!

[@DatabaseStar]

Good to hear!

[@stephenm3874]

Well presented!

[@DatabaseStar]

Thank you!

[@huntermyers1046]

Thanks, this was helpful.

[@DatabaseStar]

You're welcome!

[@shiwanginishishulkar4744]

very well explained

[@DatabaseStar]

Thanks!

[@parvezshahid8906]

YOU ARE THE BEST MATE!

[@DatabaseStar]

Thanks!

[@tajudeen2]

Nice 👍

[@DatabaseStar]

Thanks!

[@ziad9533]

I like the video and the channel content looks cool => subscribed

[@DatabaseStar]

Thanks! 😀

[@hintsoftware]

Can we use where clause or any kind of filters in any way for each windows function seperately ?
We are unable to fully utilize window function without where clauses... or any trick to do so in sql server ?

[@DatabaseStar]

Yes you should be able to use a window function and where clauses.
The window function does the calculation and the where clause does the filtering.
I don’t know if you can have separate where clauses for each window function. Perhaps surrounding it with a case statement?

[@hintsoftware]

@@DatabaseStar can you please advise how to use where clause along with over clauses in windows function ?

[@cindystokes8347]

When adding the partition, I accidentally did not grab the "order by" for the outer query and it worked perfectly. So rather than add additional code to make it work, makes more sense to let it run with less code? Discovered this completely by accident. Here is the code.
Select order_id,
order_date,
order_total,
SUM(order_total) OVER (
PARTITION BY order_date
ORDER BY order_id ASC
) AS running_total
From orders

[@DatabaseStar]

Thanks for the comment and the code. Yes, this query would run because you don't need that final Order By statement.
Without that last Order By statement, the records are not shown in any specific order. The running total calculation for each row is still correct, but because the rows can be displayed in a different order, it may seem like the running total is jumping around or not in an order.
Adding the Order By to the end will make the results look clearer or look like the running total is being shown in the right order. But, without it, the calculation is still correct as you have mentioned.

[@hasanmougharbel8030]

Hey man, glad to meet again.
I have only a simple enquiry as a new sql learner.
How order by clause differs from rank function in sql?
Thanks for taking care of this.

[@DatabaseStar]

Good question. The Order By clause will sort the data in the order you specify. The Rank function will calculate where a row will fit in an order based on a criteria, but it doesn't change how the data is ordered.

[@hasanmougharbel8030]

@@DatabaseStar
I learned that rank () should be accompanied with over and order by clauses....So rank does dictate a change in the order of records? is that true?
Here a sample of rank function in my query
select id, rank() over(order by id asc)
from val;
Thanks

[@The_Iron_Yuppie]

Could you not achieve the same results using a GROUP BY clause?

[@DatabaseStar]

Yeah you could achieve a similar result. However, window functions allow you to perform a calculation on one group of data and display the data in another group. A Group By will both display and calculate on the same group of data.

[@yourfutureself4327]

💙

[@DatabaseStar]

Thanks!

[@user-fg6mz1rh9b]

👍👍

[@DatabaseStar]

Thanks!

[@Leopar525]

BigQuery as well

[@DatabaseStar]

Good idea

[@saireddyksr]

♥️🙏🤝

[@DatabaseStar]

Thanks!

[@pging8328]

appreciate the video - but you're introducing concepts a little too quickly here - namely the frame -- the default frame - unbounded preceding to the current row - and this is a little too tricky for people to grasp without a direct explanation - the running total would make sense in light of the default frame.

[@DatabaseStar]

Thanks for the feedback! Window functions are a hard concept to understand and explain, so I appreicate the feedback on how I've explained it!