SQL Window Functions

A window function performs a calculation across a set of rows that are related to the current row, without collapsing those rows into a single output row the way GROUP BY does. Each row keeps its own place in the result set, but gains access to values computed over a “window” of nearby or related rows. Window functions are the tool of choice for ranking rows, computing running totals, comparing a row to the one before or after it, and building reports that would otherwise require awkward self-joins or correlated subqueries.

Overview: How Window Functions Work

A normal aggregate function like SUM() or AVG(), used with GROUP BY, takes many rows and produces one row per group. A window function also aggregates or ranks, but it does so without reducing the row count — every input row still appears in the output, now carrying an extra computed column. This is possible because a window function is paired with an OVER() clause that defines the “window” of rows it can see for each row being processed.

The OVER() clause has three optional pieces:

PARTITION BY — splits the rows into independent groups (partitions), similar to GROUP BY, except each row is preserved. The window function restarts its calculation for each new partition.
ORDER BY — defines the order of rows within a partition. This is essential for ranking functions and running totals, since “first,” “previous,” and “running” only make sense relative to an order.
Frame clause (ROWS BETWEEN ... AND ... or RANGE BETWEEN ... AND ...) — defines exactly which rows, relative to the current row, are included in the calculation. If you omit it, the engine picks a default: when ORDER BY is present, aggregate window functions default to RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW (a running calculation); when ORDER BY is absent, the whole partition is used.

Window functions fall into three broad families: ranking functions (ROW_NUMBER, RANK, DENSE_RANK, NTILE), value/offset functions (LAG, LEAD, FIRST_VALUE, LAST_VALUE), and ordinary aggregate functions used as windows (SUM, AVG, COUNT, MIN, MAX combined with OVER()).

Syntax

function_name(expression) OVER (
  [PARTITION BY partition_expression, ...]
  [ORDER BY sort_expression [ASC|DESC], ...]
  [ROWS|RANGE BETWEEN frame_start AND frame_end]
)

Part	Meaning
`function_name`	A ranking function (`RANK`), offset function (`LAG`), or aggregate (`SUM`) used in window mode.
`PARTITION BY`	Optional. Groups rows so the function resets for each group; omit it to treat the whole result set as one window.
`ORDER BY`	Defines row order inside each partition. Required for ranking and offset functions to be meaningful.
Frame clause	Restricts the window to a sliding range of rows relative to the current row, such as a running total.

Examples

Example 1: Ranking rows within groups with ROW_NUMBER

CREATE TABLE sales (
  id INTEGER PRIMARY KEY,
  salesperson TEXT,
  region TEXT,
  amount REAL
);

INSERT INTO sales (salesperson, region, amount) VALUES
  ('Alice', 'East', 500),
  ('Bob', 'East', 800),
  ('Carol', 'West', 650),
  ('Dave', 'West', 400),
  ('Eve', 'East', 300);

SELECT salesperson, region, amount,
       ROW_NUMBER() OVER (PARTITION BY region ORDER BY amount DESC) AS rank_in_region
FROM sales
ORDER BY region, rank_in_region;

Result:

salesperson	region	amount	rank_in_region
Bob	East	800	1
Alice	East	500	2
Eve	East	300	3
Carol	West	650	1
Dave	West	400	2

PARTITION BY region creates two independent windows — East and West — and ROW_NUMBER() restarts counting at 1 inside each one, ordered by amount descending. Notice each salesperson still appears as their own row; nothing was collapsed.

Example 2: A running total with SUM() OVER

CREATE TABLE orders (
  order_id INTEGER PRIMARY KEY,
  customer_name TEXT,
  order_date TEXT,
  amount REAL
);

INSERT INTO orders (customer_name, order_date, amount) VALUES
  ('Acme Corp', '2026-01-05', 200),
  ('Acme Corp', '2026-02-10', 150),
  ('Acme Corp', '2026-03-15', 300),
  ('Globex Inc', '2026-01-20', 500),
  ('Globex Inc', '2026-02-25', 100);

SELECT customer_name, order_date, amount,
       SUM(amount) OVER (
         PARTITION BY customer_name
         ORDER BY order_date
         ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
       ) AS running_total
FROM orders
ORDER BY customer_name, order_date;

Result:

customer_name	order_date	amount	running_total
Acme Corp	2026-01-05	200	200
Acme Corp	2026-02-10	150	350
Acme Corp	2026-03-15	300	650
Globex Inc	2026-01-20	500	500
Globex Inc	2026-02-25	100	600

The frame clause ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW tells the engine, for each row, to sum every row from the start of that customer’s partition up through the current row — a classic running total, reset per customer.

Example 3: RANK vs. DENSE_RANK with ties

CREATE TABLE test_scores (
  id INTEGER PRIMARY KEY,
  student TEXT,
  score INTEGER
);

INSERT INTO test_scores (student, score) VALUES
  ('Ann', 95),
  ('Ben', 88),
  ('Cid', 95),
  ('Dee', 72),
  ('Eli', 88);

SELECT student, score,
       RANK() OVER (ORDER BY score DESC) AS rnk,
       DENSE_RANK() OVER (ORDER BY score DESC) AS dense_rnk
FROM test_scores
ORDER BY score DESC, student;

Result:

student	score	rnk	dense_rnk
Ann	95	1	1
Cid	95	1	1
Ben	88	3	2
Eli	88	3	2
Dee	72	5	3

Ann and Cid are tied for first, so both get rank 1. RANK() then skips to 3 for the next distinct value (leaving a gap where rank 2 would have been), while DENSE_RANK() continues consecutively at 2. Choosing between them depends on whether gaps after ties matter for your report.

Example 4: Comparing a row to the previous one with LAG

CREATE TABLE monthly_revenue (
  id INTEGER PRIMARY KEY,
  month TEXT,
  revenue REAL
);

INSERT INTO monthly_revenue (month, revenue) VALUES
  ('Jan', 1000),
  ('Feb', 1200),
  ('Mar', 900),
  ('Apr', 1500);

SELECT month, revenue,
       LAG(revenue) OVER (ORDER BY id) AS prev_month_revenue,
       revenue - LAG(revenue) OVER (ORDER BY id) AS change
FROM monthly_revenue
ORDER BY id;

Result:

month	revenue	prev_month_revenue	change
Jan	1000	NULL	NULL
Feb	1200	1000	200
Mar	900	1200	-300
Apr	1500	900	600

LAG(revenue) pulls the value from the previous row in the ordered window, letting you compute month-over-month change in a single pass. There’s no previous row for January, so both the offset value and the computed change are NULL. The mirror function LEAD() looks forward instead of back.

How It Works Step by Step (Under the Hood)

SQL has a logical processing order that every query follows regardless of how it’s typed: FROM/JOIN → WHERE → GROUP BY → HAVING → window functions → SELECT (final column list) → ORDER BY → LIMIT. Window functions are evaluated after WHERE and GROUP BY/HAVING have already filtered and aggregated the base rows, but before the final ORDER BY and LIMIT are applied. That ordering explains two things: window functions can see already-grouped/aggregated data, and you cannot filter on a window function’s result directly in WHERE (see Common Mistakes below).

Physically, when the engine encounters a window function it must, for each distinct combination of PARTITION BY keys, sort the rows in that partition by the ORDER BY expression (unless an index already provides that order), then walk the sorted rows applying the frame clause to compute the function’s value at each position. This sorting step is the main performance cost of window functions — on large tables, an index on the partition and order columns can let the engine avoid a separate sort step entirely.

Common Mistakes

Mistake 1: Filtering on a window function’s alias in WHERE

SELECT student, score, RANK() OVER (ORDER BY score DESC) AS rnk
FROM test_scores
WHERE rnk = 1;

This fails because WHERE runs before window functions are computed, so the column rnk does not exist yet at that stage of processing. The fix is to compute the ranking in a subquery or CTE, then filter the outer query:

CREATE TABLE test_scores (
  id INTEGER PRIMARY KEY,
  student TEXT,
  score INTEGER
);

INSERT INTO test_scores (student, score) VALUES
  ('Ann', 95),
  ('Ben', 88),
  ('Cid', 95),
  ('Dee', 72);

WITH ranked AS (
  SELECT student, score, RANK() OVER (ORDER BY score DESC) AS rnk
  FROM test_scores
)
SELECT student, score, rnk
FROM ranked
WHERE rnk = 1;

Result: Ann, 95, 1 and Cid, 95, 1 — only the top-ranked students. Wrapping the window function in a CTE lets the outer WHERE run after the ranking has already been computed.

Mistake 2: Forgetting PARTITION BY

CREATE TABLE employees (
  id INTEGER PRIMARY KEY,
  name TEXT,
  department TEXT,
  salary REAL
);

INSERT INTO employees (name, department, salary) VALUES
  ('Alice', 'Engineering', 95000),
  ('Bob', 'Engineering', 88000),
  ('Carol', 'Sales', 72000),
  ('Dave', 'Sales', 65000);

SELECT name, department, salary,
       RANK() OVER (ORDER BY salary DESC) AS rnk
FROM employees;

This runs without error, but it’s misleading if the goal was “rank employees within their own department.” Without PARTITION BY, the ranking is computed across the entire table, so Carol (Sales) ends up ranked 3rd company-wide instead of 1st in her department. The fix adds the missing partition:

SELECT name, department, salary,
       RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS rnk
FROM employees;

Result: Alice (Engineering) rank 1, Bob (Engineering) rank 2, Carol (Sales) rank 1, Dave (Sales) rank 2 — each department now ranks independently.

Best Practices

Always include an explicit ORDER BY inside OVER() for ranking and offset functions — without it, row order (and therefore the ranking) is not guaranteed to be meaningful or stable.
Use PARTITION BY whenever you want a calculation to reset per group; forgetting it is the most common window function bug.
Be explicit about the frame clause (ROWS BETWEEN ...) for running totals instead of relying on the default, since RANGE and ROWS behave differently when there are duplicate values in the ORDER BY column.
Wrap a window function in a subquery or CTE before filtering on its result, since WHERE cannot reference it directly.
Choose ROW_NUMBER, RANK, or DENSE_RANK deliberately based on how you want ties handled — unique numbering, gapped ranking, or gapless ranking.
On large tables, consider indexing the columns used in PARTITION BY and ORDER BY, since the engine typically has to sort each partition to compute the window.
Test your query against data that actually contains ties or nulls in the ordering column — behavior that looks correct on unique, clean sample data can surprise you in production.

Practice Exercises

Exercise 1: Using an employees table with department and salary columns, write a query that returns the second-highest-paid employee in each department using DENSE_RANK().
Exercise 2: Using an orders table with customer_name, order_date, and amount, compute each customer’s running total ordered by date, then write a follow-up query (using a CTE) that returns only the first order where each customer’s running total exceeds 400.
Exercise 3: Using a monthly_revenue table, use LAG() to find every month where revenue decreased compared to the previous month. Hint: filter on the computed change column from within a CTE, not directly in WHERE.

Summary

Window functions compute a value across a set of related rows without collapsing the result the way GROUP BY does — every input row survives in the output.
OVER() defines the window via optional PARTITION BY (grouping), ORDER BY (sequencing), and a frame clause (which rows relative to the current one are included).
ROW_NUMBER, RANK, and DENSE_RANK differ only in how they handle ties — unique, gapped, or gapless numbering.
LAG and LEAD let you compare a row to the row before or after it without a self-join.
Aggregate functions like SUM and AVG become running/windowed calculations when combined with OVER() and a frame clause.
Window functions are evaluated after WHERE/GROUP BY/HAVING but before the final ORDER BY, which is why their results must be filtered from an outer query or CTE, not the same-level WHERE.

SQL Window Functions

Overview: How Window Functions Work

Syntax

Examples

Example 1: Ranking rows within groups with ROW_NUMBER

Example 2: A running total with SUM() OVER

Example 3: RANK vs. DENSE_RANK with ties

Example 4: Comparing a row to the previous one with LAG

How It Works Step by Step (Under the Hood)

Common Mistakes

Mistake 1: Filtering on a window function’s alias in WHERE

Mistake 2: Forgetting PARTITION BY

Best Practices

Practice Exercises

Summary

Table of Contents