SQL Window Functions: The Complete Guide with Examples (2026)

What Are Window Functions?

Window functions perform calculations across a set of rows that are somehow related to the current row. Unlike GROUP BY, which collapses rows into groups, window functions keep every individual row in the result while adding computed values alongside them.

The name "window" comes from the idea that the function looks through a window of rows to compute its value. You define this window using the OVER() clause.

-- GROUP BY: collapses to one row per department
SELECT department, AVG(salary) AS avg_salary
FROM employees
GROUP BY department;

-- Window function: keeps every row, adds department average alongside
SELECT
    name,
    department,
    salary,
    AVG(salary) OVER (PARTITION BY department) AS dept_avg_salary
FROM employees;

In the window function version, you see every employee with their individual salary AND the department average on the same row. This is what makes window functions so powerful for analytics: you can compare individual values to group-level aggregates without losing row-level detail.

Here is the sample data we will use throughout this guide:

CREATE TABLE sales (
    id          INT PRIMARY KEY,
    salesperson VARCHAR(50),
    region      VARCHAR(20),
    amount      DECIMAL(10,2),
    sale_date   DATE
);

INSERT INTO sales VALUES
(1,  'Alice', 'North', 500.00,  '2026-01-15'),
(2,  'Bob',   'North', 350.00,  '2026-01-20'),
(3,  'Alice', 'North', 700.00,  '2026-02-10'),
(4,  'Carol', 'South', 450.00,  '2026-02-15'),
(5,  'Bob',   'North', 600.00,  '2026-02-28'),
(6,  'Carol', 'South', 800.00,  '2026-03-05'),
(7,  'Dave',  'South', 300.00,  '2026-03-10'),
(8,  'Alice', 'North', 550.00,  '2026-03-15'),
(9,  'Dave',  'South', 650.00,  '2026-03-20'),
(10, 'Bob',   'North', 400.00,  '2026-03-25');

Window Function Syntax

Every window function follows this pattern:

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

• PARTITION BY divides rows into groups. The function resets for each group. Optional: if omitted, all rows are one partition.
• ORDER BY defines the sequence within each partition. Required for ranking and offset functions.
• Frame clause further narrows the window to a range of rows relative to the current row. Used for running totals and moving averages.

Ranking Functions: ROW_NUMBER, RANK, DENSE_RANK

Ranking functions assign a number to each row based on its position within a partition. The three ranking functions differ only in how they handle ties.

SELECT
    salesperson,
    region,
    SUM(amount) AS total_sales,
    ROW_NUMBER() OVER (ORDER BY SUM(amount) DESC) AS row_num,
    RANK()       OVER (ORDER BY SUM(amount) DESC) AS rank_num,
    DENSE_RANK() OVER (ORDER BY SUM(amount) DESC) AS dense_rank_num
FROM sales
GROUP BY salesperson, region;

salesperson	total_sales	row_num	rank_num	dense_rank_num
Alice	1750.00	1	1	1
Bob	1350.00	2	2	2
Carol	1250.00	3	3	3
Dave	950.00	4	4	4

Top N per group with ROW_NUMBER

The most common use of ROW_NUMBER is getting the top N rows per group:

-- Top 2 sales per region
WITH ranked AS (
    SELECT
        salesperson,
        region,
        amount,
        sale_date,
        ROW_NUMBER() OVER (
            PARTITION BY region
            ORDER BY amount DESC
        ) AS rn
    FROM sales
)
SELECT salesperson, region, amount, sale_date
FROM ranked
WHERE rn <= 2;

salesperson	region	amount	sale_date
Alice	North	700.00	2026-02-10
Bob	North	600.00	2026-02-28
Carol	South	800.00	2026-03-05
Dave	South	650.00	2026-03-20

This pattern is one of the most common SQL interview questions. It replaces complex self-joins with a clean, readable solution.

LAG and LEAD: Compare Consecutive Rows

LAG looks backward and LEAD looks forward in the ordered result set. They are essential for time-series analysis, calculating changes between periods, and finding trends.

-- Monthly revenue with month-over-month change
WITH monthly AS (
    SELECT
        DATE_TRUNC('month', sale_date) AS month,
        SUM(amount) AS revenue
    FROM sales
    GROUP BY DATE_TRUNC('month', sale_date)
)
SELECT
    month,
    revenue,
    LAG(revenue, 1) OVER (ORDER BY month) AS prev_month_revenue,
    revenue - LAG(revenue, 1) OVER (ORDER BY month) AS change,
    ROUND(
        (revenue - LAG(revenue, 1) OVER (ORDER BY month))
        / LAG(revenue, 1) OVER (ORDER BY month) * 100, 1
    ) AS change_pct
FROM monthly
ORDER BY month;

month	revenue	prev_month	change	change_pct
2026-01	850.00	NULL	NULL	NULL
2026-02	1750.00	850.00	900.00	105.9
2026-03	2700.00	1750.00	950.00	54.3

The first row has NULL for previous month because there is no row before it. You can provide a default value as the third argument: LAG(revenue, 1, 0) returns 0 instead of NULL.

-- LEAD: peek at the next row
SELECT
    salesperson,
    sale_date,
    amount,
    LEAD(sale_date, 1) OVER (
        PARTITION BY salesperson ORDER BY sale_date
    ) AS next_sale_date,
    LEAD(sale_date, 1) OVER (
        PARTITION BY salesperson ORDER BY sale_date
    ) - sale_date AS days_between_sales
FROM sales
ORDER BY salesperson, sale_date;

This pattern helps you measure the gap between events: time between purchases per customer, days between logins per user, or intervals between support tickets.

Running Totals and Cumulative Sums

A running total adds up values row by row as you move through the ordered result. This requires SUM with an ORDER BY inside the OVER clause.

-- Running total of sales by date
SELECT
    sale_date,
    salesperson,
    amount,
    SUM(amount) OVER (ORDER BY sale_date) AS running_total,
    SUM(amount) OVER (
        PARTITION BY region
        ORDER BY sale_date
    ) AS region_running_total
FROM sales
ORDER BY sale_date;

sale_date	salesperson	amount	running_total	region_running
2026-01-15	Alice	500.00	500.00	500.00
2026-01-20	Bob	350.00	850.00	850.00
2026-02-10	Alice	700.00	1550.00	1550.00
2026-02-15	Carol	450.00	2000.00	450.00
2026-02-28	Bob	600.00	2600.00	2150.00

Notice that the region running total resets for each region (South starts fresh at 450.00 for Carol). The overall running total keeps accumulating across all regions.

Moving Averages with Window Frames

Window frames let you define exactly which rows the function considers relative to the current row. This is how you calculate moving averages, rolling sums, and other sliding window computations.

-- 3-row moving average
SELECT
    sale_date,
    amount,
    ROUND(AVG(amount) OVER (
        ORDER BY sale_date
        ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
    ), 2) AS moving_avg_3,
    SUM(amount) OVER (
        ORDER BY sale_date
        ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
    ) AS rolling_sum_3
FROM sales
ORDER BY sale_date;

The frame ROWS BETWEEN 2 PRECEDING AND CURRENT ROW means: include the current row and the 2 rows before it. For the first row, there are no preceding rows so the average equals the single value. For the second row, only one preceding row exists.

Common frame specifications

-- All rows from start to current (running total, this is the default)
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW

-- Current row and the next 2 rows
ROWS BETWEEN CURRENT ROW AND 2 FOLLOWING

-- 3 rows before to 3 rows after (centered window)
ROWS BETWEEN 3 PRECEDING AND 3 FOLLOWING

-- All rows in the partition (same as no ORDER BY)
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING

-- RANGE vs ROWS: RANGE groups ties together
RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW

NTILE: Distribute Rows into Buckets

NTILE divides the ordered partition into a specified number of roughly equal groups and assigns a group number to each row.

-- Divide sales into quartiles
SELECT
    salesperson,
    amount,
    NTILE(4) OVER (ORDER BY amount) AS quartile
FROM sales;

Common uses include creating percentile buckets for analysis, splitting work evenly across processors, or categorizing customers into tiers:

-- Categorize customers into spending tiers
WITH customer_totals AS (
    SELECT customer_id, SUM(amount) AS total_spent
    FROM orders
    GROUP BY customer_id
)
SELECT
    customer_id,
    total_spent,
    CASE NTILE(3) OVER (ORDER BY total_spent DESC)
        WHEN 1 THEN 'High Value'
        WHEN 2 THEN 'Medium Value'
        WHEN 3 THEN 'Low Value'
    END AS tier
FROM customer_totals;

Aggregate Functions as Window Functions

Any aggregate function (SUM, AVG, COUNT, MIN, MAX) can be used as a window function by adding the OVER clause. This lets you compare individual rows to group aggregates on the same row.

-- Compare each sale to region average and total
SELECT
    salesperson,
    region,
    amount,
    ROUND(AVG(amount) OVER (PARTITION BY region), 2) AS region_avg,
    amount - ROUND(AVG(amount) OVER (PARTITION BY region), 2) AS vs_avg,
    SUM(amount) OVER (PARTITION BY region) AS region_total,
    ROUND(amount / SUM(amount) OVER (PARTITION BY region) * 100, 1) AS pct_of_region
FROM sales
ORDER BY region, amount DESC;

salesperson	region	amount	region_avg	pct_of_region
Alice	North	700.00	516.67	22.6
Bob	North	600.00	516.67	19.4
Carol	South	800.00	550.00	36.4
Dave	South	650.00	550.00	29.5

This single query gives you individual sales alongside regional context. Without window functions, you would need a self-join or a correlated subquery to get the same result.

Real-World Window Function Patterns

1. De-duplication: keep latest record per entity

-- Keep only the most recent address per customer
WITH ranked AS (
    SELECT *,
        ROW_NUMBER() OVER (
            PARTITION BY customer_id
            ORDER BY updated_at DESC
        ) AS rn
    FROM customer_addresses
)
SELECT * FROM ranked WHERE rn = 1;

2. Gap and island detection

-- Find gaps in sequential order numbers
SELECT
    order_num,
    LEAD(order_num) OVER (ORDER BY order_num) AS next_num,
    LEAD(order_num) OVER (ORDER BY order_num) - order_num AS gap
FROM orders
HAVING gap > 1;

3. Year-over-year comparison

-- Compare monthly revenue to same month last year
WITH monthly AS (
    SELECT
        DATE_TRUNC('month', order_date) AS month,
        SUM(amount) AS revenue
    FROM orders
    GROUP BY 1
)
SELECT
    month,
    revenue,
    LAG(revenue, 12) OVER (ORDER BY month) AS same_month_last_year,
    ROUND(
        (revenue - LAG(revenue, 12) OVER (ORDER BY month))
        / NULLIF(LAG(revenue, 12) OVER (ORDER BY month), 0) * 100, 1
    ) AS yoy_growth_pct
FROM monthly;

4. Cumulative distribution

-- What percentage of customers account for what percentage of revenue?
WITH customer_rev AS (
    SELECT customer_id, SUM(amount) AS total
    FROM orders GROUP BY customer_id
)
SELECT
    customer_id,
    total,
    SUM(total) OVER (ORDER BY total DESC) AS cumulative_rev,
    ROUND(
        SUM(total) OVER (ORDER BY total DESC)
        / SUM(total) OVER () * 100, 1
    ) AS cumulative_pct
FROM customer_rev
ORDER BY total DESC;

Let AI write your window function queries. Describe the analysis you need in plain English and AI2SQL generates the correct window function syntax for your database.

Frequently Asked Questions

What are window functions in SQL?

Window functions perform calculations across a set of rows related to the current row, without collapsing them into a single output row like GROUP BY does. They use the OVER() clause to define the window of rows. You can rank rows, calculate running totals, compare values with previous rows, and compute moving averages while still seeing every individual row in the result.

What is the difference between ROW_NUMBER, RANK, and DENSE_RANK?

ROW_NUMBER assigns a unique sequential number to each row (1, 2, 3, 4, 5) with no duplicates. RANK assigns the same number to tied values but skips numbers after ties (1, 2, 2, 4, 5). DENSE_RANK assigns the same number to tied values without skipping (1, 2, 2, 3, 4). Use ROW_NUMBER when you need unique numbering, RANK for competition-style ranking, and DENSE_RANK when you want consecutive ranks despite ties.

What does PARTITION BY do in window functions?

PARTITION BY divides the result set into groups (partitions) and the window function is applied independently within each group. It is similar to GROUP BY but without collapsing rows. For example, ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) numbers employees within each department separately, restarting the count at 1 for each department.

How do LAG and LEAD work in SQL?

LAG accesses a value from a previous row in the result set, while LEAD accesses a value from a subsequent row. Both require an ORDER BY clause. LAG(column, 1) gets the value from one row before the current row. LEAD(column, 1) gets the value from one row after. They are used for comparing consecutive rows, such as calculating month-over-month revenue changes or finding gaps in sequential data.

Can AI generate window function queries?

Yes. Tools like AI2SQL let you describe what you need in plain English, like "rank employees by salary within each department" or "calculate running total of sales by month," and the AI generates the correct window function query. This is particularly helpful because window function syntax can be complex with PARTITION BY, ORDER BY, and frame specifications.