SQL Window Functions Cheat Sheet

Example dataset

We will use a dataset on the sales of bicycles as a sample. This dataset includes:

The [product] table

The product table contains the types of bicycles sold, their model year, and list price.

The [order] table

The order table contains the order_id and its date.

The [order_items] table

The order_items table lists the orders of a bicycle store. For each order_id, there are several products sold (product_id). Each product_id has a discount value.

What are Window Functions?

A window function makes a calculation across multiple rows that are related to the current row. For example, a window function allows you to calculate:

• Running totals (i.e. sum values from all the rows before the current row)
• 7-day moving averages (i.e. average values from 7 rows before the current row)
• Rankings 

Similar to an aggregate function (GROUP BY), a window function performs the operation across multiple rows. Unlike an aggregate function, a window function does not group rows into one single row. 

Syntax

Windows can be defined in the SELECT section of the query.

  SELECT
    window_function() OVER(
         PARTITION BY partition_expression
         ORDER BY order_expression
         window_frame_extent
    ) AS window_column_alias
    FROM table_name

To reuse the same window with several window functions, define a named window using the WINDOW keyword. This appears in the query after the HAVING section and before the ORDER BY section.

   SELECT
    window_function() OVER(window_name)
    FROM table_name
    [HAVING ...]
    WINDOW window_name AS (
         PARTITION BY partition_expression
         ORDER BY order_expression
         window_frame_extent
    )
    [ORDER BY ...]

Order by

ORDER BY is a subclause within the OVER clause. ORDER BY changes the basis on which the function assigns numbers to rows.

It is a must-have for window functions that assign sequences to rows, including RANK and ROW_NUMBER. For example, if we ORDER BY the expression price on an ascending order, then the lowest-priced item will have the lowest rank.

Let's compare the following two queries which differ only in the ORDER BY clause

   /* Rank price from LOW->HIGH */
   SELECT
       product_name,
       list_price,
       RANK() OVER 
          (ORDER BY list_price ASC) rank
   FROM products

   /* Rank price from HIGH->LOW */
   SELECT
       product_name,
       list_price,
       RANK() OVER 
          (ORDER BY list_price DESC) rank
   FROM products

Partition by

We can use PARTITION BY together with OVER to specify the column over which the aggregation is performed. 

Comparing PARTITION BY with GROUP BY, we find the following similarity and difference:

Just like GROUP BY, the OVER subclause splits the rows into as many partitions as there are unique values in a column.

However, while the result of a GROUP BY aggregates all rows, the result of a window function using PARTITION BY aggregates each partition independently. Without the PARTITION BY clause, the result set is one single partition.

For example, using GROUP BY, we can calculate the average price of bicycles per model year using the following query.

  SELECT
   model_year,
   AVG(list_price) avg_price
FROM products
GROUP BY model_year

What if we want to compare each product’s price with the average price of that year? To do that, we use the AVG() window function and PARTITION BY the model year, as such.

   SELECT
   model_year,
   product_name,
   list_price,
   AVG(list_price) OVER 
     (PARTITION BY model_year) 
   avg_price
   FROM products

Notice how the avg_price of 2018 is exactly the same whether we use the PARTITION BY clause or the GROUP BY clause.

Window frame extent

A window frame is the selected set of rows in the partition over which aggregation will occur. Put simply, they are a set of rows that are somehow related to the current row. 

A window frame is defined by a lower bound and an upper bound relative to the current row. The lowest possible bound is the first row, which is known as UNBOUNDED PRECEDING. The highest possible bound is the last row, which is known as UNBOUNDED FOLLOWING. For example, if we only want to get 5 rows before the current row, then we will specify the range using 5 PRECEDING.

Accompanying Material 

You can use this https://bit.ly/3scZtOK to run any of the queries explained in this cheat sheet.

Ranking window functions

There are several window functions for assigning rankings to rows. Each of these functions requires an ORDER BY sub-clause within the OVER clause. 

The following are the ranking window functions and their description:

We can use these functions to rank the product according to their prices.

   /* Rank all products by price */
SELECT
   product_name,
   list_price,
   ROW_NUMBER() OVER (ORDER BY list_price) AS row_num,
   DENSE_RANK() OVER (ORDER BY list_price) AS dense_rank,
   RANK() OVER (ORDER BY list_price) AS rank,
   PERCENT_RANK() OVER (ORDER BY list_price) AS pct_rank,
   NTILE(75) OVER (ORDER BY list_price) AS ntile,
   CUME_DIST() OVER (ORDER BY list_price) AS cume_dist
FROM products

Value window functions

FIRST_VALUE() and LAST_VALUE() retrieve the first and last value respectively from an ordered list of rows, where the order is defined by ORDER BY.

To compare the price of a particular bicycle model with the cheapest (or most expensive) alternative, we can use the FIRST_VALUE (or LAST_VALUE). 

   /* Find the difference in price from the cheapest alternative */
SELECT
  product_name,
  list_price,
  FIRST_VALUE(list_price) OVER (
     ORDER BY list_price 
     ROWS BETWEEN 
         UNBOUNDED PRECEDING
         AND 
         UNBOUNDED FOLLOWING 
     ) AS cheapest_price,
FROM products

  /* Find the difference in price from the priciest alternative */
SELECT
   product_name,
   list_price,
   LAST_VALUE(list_price) OVER (
     ORDER BY list_price 
     ROWS BETWEEN 
        UNBOUNDED PRECEDING 
        AND 
        UNBOUNDED FOLLOWING 
     ) AS highest_price
FROM products

Aggregate window functions

Aggregate functions available for GROUP BY, such as COUNT(), MIN(), MAX(), SUM(), and AVG() are also available as window functions.

Suppose we want to find the average, maximum and minimum discount for each product, we can achieve it as such.

SELECT
   order_id,
   product_id,
   discount,
   AVG(discount) OVER (PARTITION BY product_id) AS avg_discount,
   MIN(discount) OVER (PARTITION BY product_id) AS min_discount,
   MAX(discount) OVER (PARTITION BY product_id) AS max_discount
FROM order_items

LEAD, LAG

The LEAD and LAG locate a row relative to the current row.

Both LEAD and LAG take three arguments:

• Expression: the name of the column from which the value is retrieved
• Offset: the number of rows to skip. Defaults to 1.
• Default_value: the value to be returned if the value retrieved is null. Defaults to NULL.

With LAG and LEAD, you must specify ORDER BY in the OVER clause.

LEAD and LAG are most commonly used to find the value of a previous row or the next row. For example, they are useful for calculating the year-on-year increase of business metrics like revenue.

Here is an example of using lag to compare this year's sales to last year's.

   /* Find the number of orders in a year */
WITH yearly_orders AS (
   SELECT
	year(order_date) AS year,
	COUNT(DISTINCT order_id) AS num_orders
	FROM sales.orders
	GROUP BY year(order_date)
)
 
/* Compare this year's sales to last year's */
SELECT
   *,
   LAG(num_orders) OVER (ORDER BY year) last_year_order,
   LAG(num_orders) OVER (ORDER BY year) - num_orders diff_from_last_year
FROM yearly_orders

Similarly, we can make a comparison of each year's order with the next year's.

   /* Find the number of orders in a year */
WITH yearly_orders AS (
   SELECT
	year(order_date) AS year,
	COUNT(DISTINCT order_id) AS num_orders
	FROM sales.orders
	GROUP BY year(order_date)
)
 
/* Compare the number of years compared to next year */
SELECT *,
   LEAD(num_orders) OVER (ORDER BY year) next_year_order,
   LEAD(num_orders) OVER (ORDER BY year) - num_orders diff_from_next_year
FROM yearly_orders