What are Window Functions?

> P artition by
A window function makes a calculation across multiple rows that are related to We can use PARTITION BY together with OVER to specify the column over

SQL for Data Science

which the aggregation is performed.

the current row. For example, a window function allows you to calculate.
C omparing PARTITION BY with GROUP BY, we find the following similarity and
unning totals (i.e. sum values from all the rows before the current row
SQL Window Functions
R
difference
7-day moving averages (i.e. average values from 7 rows before the current row
Rankings

Just like GROUP BY, the OVER subclause splits the rows into as many partitions
Learn SQL online at [Link]

as there are unique values in a column.

Similar to an aggregate function (GROUP BY), a window function performs the
owever, while the result of a GROUP BY aggregates all rows, the result of a
operation across multiple rows. Unlike an aggregate function, a window function
H

window function using PARTITION BY aggregates each partition

does not group rows into one single row.
independently. Without the PARTITION BY clause, the result set is one single
partition.

> Example dataset AGGR EGATE 


FUNCTION
WINDOW 

FUNCTION

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

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

includes: SELECT

model_year,

AVG(list_price) avg_price

The [product] table

FROM products

GROUP BY model_year

The product table contains the types of bicycles sold, their model year, and list What if we want to compare each product’s price with the average price of
price.
that year? To do that, we use the AVG() window function and PARTITION BY the

product_id product_name model_year list_price

> Syntax model year, as such.

SELECT

1 Treak 820 - 2016 2016 379.99 Windows can be defined in the SELECT section of the query.
model_year,

product_name,

2 Ritchey Timberwolf Frameset - 2016 2016 749.99 SELECT

list_price,

windo w f
_ unction() OVER (
AVG(list_price) OVER

3 Surly Wednesday Frameset - 2016 2016 999.99 PARTITI N O BY partition_e xpressio n

(PARTITION BY model_year) 


ORD ER BY order_e x
pressio n
avg_price

4 Trek Fuel EX 8 29 - 2016 2016 2899.99 window f _ rame_e xten t

FROM products

) AS window _column_alias

5 Heller Shagamaw Frame - 2016 2016 1320.99 b

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

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

The order table contains the order_id and its date. SELECT

window f _ unction() OVER( windo w _name )

> W d w f ame exte t

in o r n
FROM b ta le_name

order_id order_date [H IN ...]

AV G A window frame is the selected set of rows in the partition over which
WIND W w
O w indo _name AS (
aggregation will occur. Put simply, they are a set of rows that are somehow
1 2016-01-01T00:00:00.000Z TITI N x n

D
PAR

OR ER
O

BY
BY

order_e
partition_e

x
pressio
pressio

related to the current row.

w w f
indo _ rame_e xten t

2 2016-01-01T00:00:00.000Z )
A window frame is defined by a lower bound and an upper bound relative to
[ D
OR ER ...]
BY
the current row. The lowest possible bound is the first row, which is known as
3 2016-01-02T00:00:00.000Z
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

> Order by
4 2016-01-03T00:00:00.000Z
rows before the current row, then we will specify the range using 5 PRECEDING.

5 2016-01-03T00:00:00.000Z

OR DE is a subclause within the OVER clause. ORDER BY changes the basis on

R BY UNBOUNDED

PRECEDING
which the function assigns numbers to rows.

The [order_items] table

N PRECEDING

t is a must-have for window functions that assign sequences to rows, including

I N ROWS
RANK and ROW_NUMBER. For example, if we ORDER BY the expression `price` on CURRENT ROW
The order_items table lists the orders of a bicycle store. For each order_id, there
an ascending order, then the lowest-priced item will have the lowest rank.
M ROWS
are several products sold (product_id). Each product_id has a discount value.

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

order_id product_id discount UNBOUNDED

/* Ran k price from W->HI H */

LO G /* Rank price f
rom HI H-> W */

G LO FOLLOWING
1 20 0.2 SELECT
SELECT

product_name,
product_name,

1 8 0.07 list_price,
list_price,

RA NK () OVER
RA NK () OVER

1 10 0.05
> Accompanying Material
(OR DE R BY ist p ic
l _ r e SC
A ) rank
DE
(OR R BY ist p ic
l _ r e SC
A ) k

ran

1 16 0.05
FROM products
FROM products

1 4 0.2
You can use this [Link] to run any of the queries explained in
2 20 0.07
this cheat sheet.
 > V alue window functions > LEAD, LAG
FIRST_VALUE() and LAST_VALUE() retrieve the first and last value respectively The LEAD and LAG locate a row relative to the current row.
SQL for Data Science from an ordered list of rows, where the order is defined by ORDER BY.

Value window function Function Function Syntax Function Description

SQL Window Functions FIRST_VALUE(value_to_return) OVER

(ORDER BY value_to_order_by)

Returns the first value in an ordered set of

values

LEAD(expression
[,offset[,default_value]])
OVER(ORDER BY columns)

Accesses the value stored in a row after the

current row.

Learn SQL online at [Link] LAST_VALUE(value_to_return) OVER Returns the last value in an ordered set of LAG(expression Accesses the value stored in a row before
(ORDER BY value_to_order_by) values [,offset[,default_value]]) the current row.
OVER(ORDER BY columns)
NTH_VALUE(value_to_return, n) OVER Returns the nth value in an ordered set of
(ORDER BY value_to_order_by) values.
Both LEAD and LAG take three arguments
To compare the price of a particular bicycle model with the cheapest (or most Expression: the name of the column from which the value is retrieve

> Ranking window functions expensive) alternative, we can use the FIRST_VALUE (or LAST_VALUE).

/* Find the difference in price from /* Find the difference in price from
Offset: the number of rows to skip. Defaults to 1
Default_value: the value to be returned if the value retrieved is null.
the cheapest alternative */
the priciest alternative */
Defaults to NULL.

There are several window functions for assigning rankings to rows. Each of SELECT
SELECT

product_name,
product_name,

these functions requires an ORDER BY sub-clause within the OVER clause.

list_price,
list_price,

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

FIRST_VALUE(list_price) OVER (
LAST_VALUE(list_price) OVER (

ORDER BY list_price
ORDER BY list_price
LEAD and LAG are most commonly used to find the value of a previous row or
The following are the ranking window functions and their description: ROWS BETWEEN
ROWS BETWEEN

UNBOUNDED PRECEDING
UNBOUNDED PRECEDING
the next row. For example, they are useful for calculating the year-on-year
AND
AND
increase of business metrics like revenue.

Function Syntax Function Description Additional notes UNBOUNDED FOLLOWING

UNBOUNDED FOLLOWING

) AS cheapest_price,
) AS highest_price

FROM products

FROM products

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

ROW_NUMBER()
Assigns a sequential integer Row numbers are not repeated within
to each row within the each partition.

/* Find the number of orders in a year */

partition of a result set.

WITH yearly_orders AS (

SELECT

year(order_date) AS year,

RANK()

Assigns a rank number to Tied values are given the same rank
COUNT(DISTINCT order_id) AS num_orders

each row in a partition.

The next rankings are skipped. FROM [Link]

GROUP BY year(order_date)

PERCENT_RANK() Assigns the rank number of Tied values are given the same rank

> Aggregate window functions

each row in a partition as a Computed as the fraction of rows

/* Compare this year's sales to last year's */

percentage.

less than the current row, i.e., the SELECT

rank of row divided by the largest *,

LAG(num_orders) OVER (ORDER BY year) last_year_order,

Aggregate functions available for GROUP BY, such as COUNT(), MIN(), MAX(),
rank in the partition.
LAG(num_orders) OVER (ORDER BY year) - num_orders diff_from_last_year

SUM(), and AVG() are also available as window functions.

FROM yearly_orders

NTILE(n_buckets) Distributes the rows of a For example, if we perform the

partition into a specified window function NTILE(5) on a table
number of buckets.

with 100 rows, they will be in bucket Function Syntax Function Description
1, rows 21 to 40 in bucket 2, rows 41 ount the number of rows that have a non-
COUNT(expression) OVER (PARTITION C

to 60 in bucket 3, et cetera.
null expression in the partition.

BY partition_column)

MIN(expression) OVER (PARTITION BY Find the minimum of the expression in the

CUME_DIST() The cumulative distribution: the t returns a value larger than 0 and
I
partition_column)

partition.

percentage of rows less than or at most 1.

equal to the current row.

Tied values are given the same

Similarly, we can make a comparison of each year's order with the next year's.
MAX(expression) OVER (PARTITION BY Find the maximum of the expression in the
cumulative distribution value.

partition_column)

partition.

/* Find the number of orders in a year */

WITH yearly_orders AS (

AVG(expression) OVER (PARTITION BY Find the mean (average) of the expression SELECT

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

in the partition.

year(order_date) AS year,

COUNT(DISTINCT order_id) AS num_orders

/* Rank all products by price */

FROM [Link]

SELECT
Suppose we want to find the average, maximum and minimum discount for GROUP BY year(order_date)

product_name,
)

list_price,
each product, we can achieve it as such.

ROW_NUMBER() OVER (ORDER BY list_price) AS row_num,

/* Compare the number of years compared to next year */

DENSE_RANK() OVER (ORDER BY list_price) AS dense_rank,

SELECT
SELECT *,

RANK() OVER (ORDER BY list_price) AS rank,

order_id,
LEAD(num_orders) OVER (ORDER BY year) next_year_order,

PERCENT_RANK() OVER (ORDER BY list_price) AS pct_rank,

product_id,
LEAD(num_orders) OVER (ORDER BY year) - num_orders diff_from_next_year

NTILE(75) OVER (ORDER BY list_price) AS ntile,

discount,
FROM yearly_orders

CUME_DIST() OVER (ORDER BY list_price) AS cume_dist

AVG(discount) OVER (PARTITION BY product_id) AS avg_discount,

FROM products

MIN(discount) OVER (PARTITION BY product_id) AS min_discount,

MAX(discount) OVER (PARTITION BY product_id) AS max_discount

FROM order_items