EssentialSQL: SQL Window Functions for Business Analytics

Understand the basic definition of a window function and what makes them different to other functions you use in SQL.

See the difference between how a GROUP BY query summarizes results versus a window function's ability to place summary information alongside a query result's detail rows.

In this lesson let's look at two ways you can create a running total using SQL.  We'll compare using an inner join, specifically a non-equi join, to a window function.

In this module well cover basic Windowing and Partitions.  We'll focus on using the SUM(), MAX(), MIN(), and AVG() function for our examples.

Understand when you can only use window functions within the SELECT or ORDER BY clauses.

Learn how to use OVER() to create a window.   It defines a window or set of rows to work with per underlying rows in our result set.

PARTITION BY lets you logically group rows within the window. The window function result’s reset after each partition.

Learn to use CTEs (Common Table Expressions) or Derived Tables to over come a window function's main limitation.

A window function operates on a window of rows in a query result set and returns a single value for each row. Window frames determine the set of rows on which a window function operates, similar to the way that a WHERE clause filters rows for a regular SELECT statement. The window frame can be defined using offsets relative to the current row or using a range of rows based on values in the rows.

Learn how to use ORDER By to logically order rows within a window function partition.  It is the first step in defining a frame.

In this lesson we'll cover terms regarding Partitions, Window Frames, and the Current Row.  This sets the stage for use to dig into examples in future lessons.

Learn to use the ROWS clause to define a window frame within the partition.

We'll go over some SQL to show you how to use ROWS to define a window frame.

Learn how RANGE differs from ROW in setting up a window frame.

We'll go over some SQL to show the difference between using RANGE and ROWS.  We'll highlight the differences using the COUNT() function.

In this section we'll go over the aggregate function you can use with the OVER() clause.

In this section we'll review Common Functions used with Window Functions such as MIN, MAX, SUM, and AVG.

In this section we'll review uncommonly used aggregate functions.

Create a summary result using window functions. 

Introduction to windows ranking functions.

In this section you learn how ROW_NUMBER, RANK, DENSE_RANK, and NTILE differ.

Use the WINDOW clause to define partition and order by specifications.  They make it easier to manage partitions when you have many window functions within your query.

Introduction to Analytic Functions

In this lecture we'll look at how LEAD, LAG, FIRST_ROW, and LAST_ROW are used within partitions.

Demonstration on how to use LEAD and LAG as window functions.

Demonstration showing how FIRST_VALUE and LAST_VALUE are used within Partitions.

Explore using CUME_DIST and PERCENT_RANK as window functions.

How do you create a running total on a column using SQL?

How do you create a moving average using windows functions in SQL?

You want to find duplicate values within a table and be able to identify the duplicates by their unique identifier.

You want to find and remove all but one row for a set of duplicates rows using SQL.

You need to find the median of a set a value. This isn’t a built-in function, so some other means is required to calculate.

You would like to calculate the percentage change from one value to the next base on the previous row to the current.

Learn how to use ROW_NUMBER() to generate ID values.