From Excel to SQL - Window Functions (OVER)

We love Excel because it allows us to reference rows relative to our current row. Window functions (and the OVER clause) are the features that enable SQL to do the same thing!

The dbfiddle website can be accessed for free and without an account at:

https://dbfiddle.uk/

Make sure to use the SQL Server dialect and the AdventureWorks sample database to be able to follow along with these examples!

---------------------------------------------------

The documentation that I showed for the various functions can be found at:

https://learn.microsoft.com/en-us/sql...
https://learn.microsoft.com/en-us/sql...
https://learn.microsoft.com/en-us/sql...

---------------------------------------------------

This is part of the From Excel to SQL playlist, available at:

   • From Excel to SQL  

Check out the previous video at:

   • From Excel to SQL - Subqueries  

...and the next video at:

   • From Excel to SQL - Final Notes  

The written version of this content is also available at:

https://bilbottom.github.io/sql-learn...

---------------------------------------------------

CHAPTERS

00:00 dbfiddle
00:28 Intro
01:49 SQL Server window functions
02:42 Aggregate functions with OVER
05:56 OVER with a partition (PARTITION BY)
08:35 Comparison with the GROUP BY
09:35 Comparison with Excel's COUNTIF
10:45 Setting up a better example
15:04 Using the LAG window function
17:58 Cumulative windows
25:20 Sliding windows
30:33 Putting it all together
31:37 Wrap up