SQL Analytic/Window Functions: Tutorial and Examples

SQL analytic ("window") functions compute a value for each row of input based on other rows, collectively known as a "window". The window is defined using OVER and can be refined using PARTITION BY, ORDER BY, and ROWS/RANGE BETWEEN. You can call functions like RANK, ROW_NUMBER, LEAD, LAG, and SUM on that window. This video will demystify these advanced SQL keywords.

For sample code, written explanations, and bonus content, visit the companion blog post at https://blog.dicksontsai.com/2020/11/....

Some names of concepts come from Google's BigQuery documentation article on analytic function concepts: https://cloud.google.com/bigquery/doc....

0:00 Overview of concepts
2:07 Example 1: Rank
4:04 Example 2: Cumulative Sum
7:06 Example 3: Moving Average
8:34 Example 4: Range
11:04 Example 5: Navigational Functions

• #education
• #sql
• #analytic_functions
• #partition_by