SQL Window Functions

Window functions in SQL allow you to perform calculations across a set of rows related to the current row, without collapsing the result set like regular aggregates do. They're incredibly powerful for analytics, ranking, running totals, and more.

This blogcovers the basics, syntax, common functions, and examples. (Note: Syntax is standard SQL; minor variations exist in databases like PostgreSQL, SQL Server, MySQL, etc.)

Here's a comprehensive visual cheat sheet for quick reference:

learnsql.com

Basic Syntax

window_function() OVER (
    [PARTITION BY column1, column2, ...]
    [ORDER BY column3 [ASC|DESC], ...]
    [frame_clause]
)

• PARTITION BY: Divides rows into groups (like GROUP BY, but keeps all rows).
• ORDER BY: Defines the order within each partition (required for many functions like running totals).
• Frame Clause (optional, for aggregates): Specifies the range of rows, e.g., ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW.

Default frame clause: is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW.

Common Window Functions

Ranking Functions

• ROW_NUMBER(): Assigns unique sequential numbers (1, 2, 3, ...). Handles ties arbitrarily, no gaps.
• RANK(): Assigns rank with ties sharing the same number; gaps in ranking after ties.
• DENSE_RANK(): Assigns rank with ties sharing the same number; no gaps.
• NTILE(n): Divides into n buckets (1 to n).

dataquest.io

Value Functions

• LAG(expr, offset=1): Value from previous row(s).
• LEAD(expr, offset=1): Value from next row(s).
• FIRST_VALUE(expr): First value in window.
• LAST_VALUE(expr): Last value in window (needs proper frame).
• NTH_VALUE(expr, n): nth value in window.

Example for LAG/LEAD:

learnsql.com

Aggregate Functions as Window

Common aggregates (SUM, AVG, COUNT, MAX, MIN) can be used over windows for running totals, moving averages, etc.

Examples of running totals and moving averages:

learnsql.com

Other Key Examples

Assume table sales with columns: region, date, amount.

Percentage of Total

SELECT region, amount,
       amount / SUM(amount) OVER (PARTITION BY region) * 100 AS pct_of_region
FROM sales;

Previous Amount (LAG)

SELECT date, amount,
       LAG(amount) OVER (ORDER BY date) AS prev_amount
FROM sales;

Rank by Amount per Region

SELECT region, amount,
       RANK() OVER (PARTITION BY region ORDER BY amount DESC) AS rank
FROM sales;

Moving Average (3-day)

SELECT date, amount,
       AVG(amount) OVER (ORDER BY date
                         ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS moving_avg
FROM sales;

Running Total

SELECT region, date, amount,
       SUM(amount) OVER (PARTITION BY region ORDER BY date
                         ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_total
FROM sales;

Window Frame Clauses

• ROWS UNBOUNDED PRECEDING: From start of partition to current.
• ROWS BETWEEN 3 PRECEDING AND CURRENT ROW: Last 4 rows (including current).
• RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW: Logical range (for dates/numbers).
• ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING: Current to end.

learnsql.com