Back to articles list Articles Cookbook
7 minutes read

SQL Window Functions Cheat Sheet

Unlock the full potential of SQL with our comprehensive Window Functions Cheat Sheet! This indispensable guide is designed to elevate your analytics capabilities and make complex data manipulations effortlessly accessible.

Welcome to the ultimate resource for mastering SQL window functions - the SQL Window Functions Cheat Sheet! This invaluable resource provides you with the essential syntax, a comprehensive list of window functions, and real-life examples to enhance your SQL skills and analytics capabilities. Designed to serve both beginners and experienced professionals, this cheat sheet is your passport to becoming proficient in SQL window functions, which are critical for performing complex data manipulations and analyses.

Download the SQL Window Functions Cheat Sheet in your preferred PDF format to have a handy reference at your fingertips:

Alternatively, you can also download the cheat sheet in PNG as a quick reference. To save, right-click (for desktop users) or long tap (for mobile users) on the image.

SQL Window Functions Cheat Sheet page 1 SQL Window Functions Cheat Sheet page 2

Window Functions

Window functions compute their result based on a sliding window frame, a set of rows that are somehow related to the current row.

Moving window frame

Aggregate Functions vs. Window Functions

Unlike aggregate functions, window functions do not collapse rows.

Side-by-side comparison of aggregate functions and window functions

Syntax

SELECT city, month, 
  sum(sold) OVER (
    PARTITION BY city 
    ORDER BY month 
    RANGE UNBOUNDED PRECEDING) total
FROM sales;
SELECT <column_1>, <column_2>,
  <window_function> OVER (
    PARTITION BY <...>
    ORDER BY <...>
    <window_frame>) <window_column_alias>
FROM <table_name>;

Named Window Definition

SELECT country, city,
  rank() OVER country_sold_avg
FROM sales
WHERE month BETWEEN 1 AND 6
GROUP BY country, city
HAVING sum(sold) > 10000
WINDOW country_sold_avg AS (
  PARTITION BY country 
  ORDER BY avg(sold) DESC)
ORDER BY country, city;
SELECT <column_1>, <column_2>,
  <window_function>() OVER <window_name>
FROM <table_name>
WHERE <...>
GROUP BY <...>
HAVING <...>
WINDOW <window_name> AS (
  PARTITION BY <...>
  ORDER BY <...>
  <window_frame>)
ORDER BY <...>;

PARTITION BY, ORDER BY, and window frame definition are all optional.

PARTITION BY

The PARTITION BY clause in SQL is used for segmenting your data into multiple groups, called partitions, allowing you to apply the window function to each partition separately, as if it were a standalone data set.

SELECT 
  city, 
  month, 
  sum(sold) OVER (PARTITION BY city) AS sum
FROM sales;
Example of PARTITION BY clause in window functions

Default Partition: With no PARTITION BY clause, the entire result set is the partition.

To dive deeper into the nuances and applications of PARTITION BY, check out our articles: How to Use the SQL PARTITION BY With OVER and How to Use the PARTITION BY Clause in SQL. These guides will equip you with the knowledge and examples you need to effectively partition your data and maximize the utility of SQL window functions.

ORDER BY

ORDER BY specifies the order of rows in each partition to which the window function is applied.

SELECT city, month, 
  sum(sold) OVER (PARTITION BY city ORDER BY month) sum
FROM sales;
Example of ORDER BY clause in window functions

Default ORDER BY: With no ORDER BY clause, the order of rows within each partition is arbitrary.

Window Frame

A window frame is a set of rows that are somehow related to the current row. The window frame is evaluated separately within each partition.

Bounds in window frame definition
ROWS | RANGE | GROUPS BETWEEN lower_bound AND upper_bound

The bounds can be any of the five options:

  • UNBOUNDED PRECEDING
  • n PRECEDING
  • CURRENT ROW
  • n FOLLOWING
  • UNBOUNDED FOLLOWING

The lower_bound must be BEFORE the upper_bound.

Example for ROWS, RANGE, GROUPS clauses in window frame definition

As of 2024, GROUPS is only supported in PostgreSQL 11 and up.

Abbreviations

AbbreviationMeaning
UNBOUNDED PRECEDINGBETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
n PRECEDINGBETWEEN n PRECEDING AND CURRENT ROW
CURRENT ROWBETWEEN CURRENT ROW AND CURRENT ROW
n FOLLOWINGBETWEEN AND CURRENT ROW AND n FOLLOWING
UNBOUNDED FOLLOWINGBETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING

Default Window Frame

  • If ORDER BY is specified, then the frame is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW.
  • Without ORDER BY, the frame specification is ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING.

Logical Order of Operations in SQL

  1. FROM, JOIN
  2. WHERE
  3. GROUP BY
  4. aggregate functions
  5. HAVING
  6. window functions
  7. SELECT
  8. DISTINCT
  9. UNION/INTERSECT/EXCEPT
  10. ORDER BY
  11. OFFSET
  12. LIMIT/FETCH/TOP

You can use window functions in SELECT and ORDER BY. However, you can't put window functions anywhere in the FROM, WHERE, GROUP BY, or HAVING clauses.

List of Window Functions

  • Ranking Functions
    • row_number()
    • rank()
    • dense_rank()
  • Distribution Functions
    • percent_rank()
    • cume_dist()
  • Analytic Functions
    • lead()
    • lag()
    • ntile()
    • first_value()
    • last_value()
    • nth_value()
  • Aggregate Functions
    • avg()
    • count()
    • max()
    • min()
    • sum()

Ranking Functions

  • row_number() - unique number for each row within partition, with different numbers for tied values
  • rank() - ranking within partition, with gaps and same ranking for tied values
  • dense_rank() - ranking within partition, with no gaps and same ranking for tied values
Example for window functions row_number(), rank(), dense_rank()

ORDER BY and Window Frame: rank() and dense_rank() require ORDER BY, but row_number() does not require ORDER BY. Ranking functions do not accept window frame definition (ROWS, RANGE, GROUPS).

Ranking functions in SQL are essential tools for assigning a rank to each row in a partition. We recommend our series of article on ranking functions:

Distribution Functions

  • percent_rank() - the percentile ranking number of a row—a value in [0, 1] interval: (rank-1) / (total number of rows - 1)
  • cume_dist() - the cumulative distribution of a value within a group of values, i.e., the number of rows with values less than or equal to the current row’s value divided by the total number of rows; a value in (0, 1] interval
Example for window functions cume_dist(), percent_rank()

ORDER BY and Window Frame: Distribution functions require ORDER BY. They do not accept window frame definition (ROWS, RANGE, GROUPS).

Analytic Functions

  • lead(expr, offset, default) - the value for the row offset rows after the current; offset and default are optional; default values: offset = 1, default = NULL
  • lag(expr, offset, default) - the value for the row offset rows before the current; offset and default are optional; default values: offset = 1, default = NULL
Example for window functions lead() and lag()

For a deep dive into lead() and functions, we recommend our article on The LAG Function and the LEAD Function in SQL, which provides a comprehensive guide on these functions.

  • ntile(n) - divide rows within a partition as equally as possible into n groups, and assign each row its group number.
Example for window function ntile()

ORDER BY and Window Frame: ntile(), lead(), and lag() require an ORDER BY. They do not accept window frame definition (ROWS, RANGE, GROUPS).

  • first_value(expr) - the value for the first row within the window frame
  • last_value(expr) - the value for the last row within the window frame
Example for window functions first_value(), last_value()

Note: You usually want to use RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING with last_value(). With the default window frame for ORDER BY, RANGE UNBOUNDED PRECEDING, last_value() returns the value for the current row.

  • nth_value(expr, n) - the value for the n-th row within the window frame; n must be an integer
Example for window function nth_value()

ORDER BY and Window Frame: first_value(), last_value(), and nth_value() do not require an ORDER BY. They accept window frame definition (ROWS, RANGE, GROUPS).

Aggregate Functions

  • avg(expr) - average value for rows within the window frame
  • count(expr) - count of values for rows within the window frame
  • max(expr) - maximum value within the window frame
  • min(expr) - minimum value within the window frame
  • sum(expr) - sum of values within the window frame

ORDER BY and Window Frame: Aggregate functions do not require an ORDER BY. They accept window frame definition (ROWS, RANGE, GROUPS).