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/t-sql/functions/aggregate-functions-transact-sql - https://learn.microsoft.com/en-us/sql/t-sql/functions/ranking-functions-transact-sql - https://learn.microsoft.com/en-us/sql/t-sql/functions/analytic-functions-transact-sql --------------------------------------------------- This is part of the From Excel to SQL playlist, available at: - https://www.youtube.com/playlist?list=PLEiRgvTilK5rhnVPQ_Tj3Q-CI0rGn_uiD Check out the previous video at: - https://youtu.be/pcD_7n7zKFw ...and the next video at: - https://youtu.be/yIfNUC6cVpo The written version of this content is also available at: - https://billwallis.github.io/sql-learning-materials/from-excel-to-sql/main-concepts/window-functions/ --------------------------------------------------- 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