Udemy: EssentialSQL: SQL Window Functions for Business Analytics

What you'll learn
Learn how to incorporate window functions into your SQL queries.
Understand how partitions and windows work together within a query.
Apply window functions so you can avoid using cursors in your SQL.
Understand the difference between window function mechanics and GROUB BY summaries.
Create complex queries involving joins using window functions.
Apply aggregate, analytic, and ranking functions to perform common business tasks such as calculating running totals or comparing one row's value to the next.
Create queries using either MySQL, PostgreSQL, or SQL Server.


Course content

Introduction5 lectures • 22min
 Welcome to the Course
 Why You Should Learn Windows Functions
 What Flavor of SQL Should I Choose?
 How to Take the Course
 Tools Used and Database Setup

Warmup3 lectures • 31min
 What is a Window Function?
 GROUP BY versus Window Function Results
 Getting to Know Window Functions
 Create Running Totals using SQL Joins versus Window Functions
 Write Your First Windows Function

Windows and Partitions5 lectures • 45min
 Introduction
 Window Function Order of Operations
 Using OVER to create a window
 Using PARTITION BY to group rows
 Introduction to Partition By
 Using Multiple Window Functions
 Replacing Subquery with Window Function
 Overcome Window Function Limitations
 Overcome Window Function Limitations
 Case Study in using Window Functions to Summarize Data

Defining Window Function Frames with ROW and RANGE7 lectures • 38min
 Introduction to Window Frames
 Using Over and Order by with Window Functions
 Coding Exercise – Over and Order By #1
 Coding Exercise – Over and Order By #2
 Coding Exercise – Over and Order By #3
 Introduction to Defining Window Frames using ROWS and RANGES
 Using ROWS to Define the Window Frame.
 Demonstration on Using ROWS to Define the Window Frame
 Using ROWS to Define the Window Frame #1
 Using ROWS to Define the Window Frame #2
 Using ROWS to Define the Window Frame #3
 Using RANGE to define the Window Frame
 Demonstration on Using RANGE to Define a Windows Frame
 Using Range to Define the Frame #2
 Using Range to Define the Frame #1
 Section Review (Evolution 1 of 3)
 Section Review (Evolution 2 of 3)
 Section Review (Evolution 3 of 3)

Aggregate Functions4 lectures • 9min
 Introduction to Aggregate Functions with OVER
 Commonly Used Aggregate Functions
 Uncommonly Used Aggregate Functions
 Coding Exercise - Aggregate Functions and OVER()

Ranking Functions3 lectures • 16min
 Introduction and Review of Functions
 Comparison of Ranking Functions
 Using the WINDOWS clause.
 Ranking Weekly Radio Sales
 Ranking All Product Sales
 Ranking the top 10% Product Sales

Analytic Functions5 lectures • 23min
 Introduction to Analytic Functions
 Comparison of LEAD, LAG, FIRST_ROW and LAST_ROW functions
 LEAD and LAG Demo
 FIRST_VALUE and LAST_VALUE Demo
 Lead and Lag #1
 Lead and Lag #2
 Lead and Lag #3
 Calculating Percentiles using Window Functions
 Working with Percent Ranks
 Working with Cumulative Distributions

Window Function Recipies7 lectures • 18min
 How Do I Calculate a Running Total Using SQL?
 How Do I Calculate a Moving Average using SQL?
 How Do I Find Duplicates Using Windows Functions
 How Do I Remove Duplicates Using ROW_NUMBER?
 How Do I Calculate the Median value using SQL?
 Calculate the Percentage Change from One Value to the Next
 How to Compare Two Moving Averages

Bounus1 lecture • 1min
 Bonus Lesture