By Matthew Rathbone · November 2, 2026

A window function calculates a value across a set of rows related to the current row, but unlike GROUP BY it does not merge those rows into one. You get your original rows back, with the extra calculation added as a new column. That makes window functions the tool for rankings, running totals, "compared to the previous row" and "share of the group total" questions.

If you are comfortable with SELECT and GROUP BY, you can learn this in about 20 minutes. Jump to: sample data · the OVER clause · PARTITION BY · ranking functions · running totals · LAG and LEAD · frames and moving averages · common mistakes · FAQ

Set up the sample data

The examples use two small tables. The syntax is standard and works in SQLite (3.25+), PostgreSQL, MySQL 8+, SQL Server and Oracle. Paste this into any SQL client, such as Beekeeper Studio, and run each query yourself.

CREATE TABLE employees (id INTEGER PRIMARY KEY, name TEXT, department TEXT, salary INTEGER);
INSERT INTO employees VALUES
  (1, 'Ana', 'Engineering', 150000), (2, 'Ben', 'Engineering', 150000),
  (3, 'Chloe', 'Engineering', 120000), (4, 'Dev', 'Sales', 90000),
  (5, 'Emma', 'Sales', 110000), (6, 'Felix', 'Sales', 70000);

CREATE TABLE sales (sale_date TEXT, amount INTEGER);
INSERT INTO sales VALUES
  ('2026-01-01', 100), ('2026-01-02', 150), ('2026-01-03', 90),
  ('2026-01-04', 200), ('2026-01-05', 120);

What does the OVER clause do?

A window function is any function followed by OVER (...). The OVER clause defines the "window" of rows the function looks at. With empty parentheses, the window is the whole result set:

SELECT name, department, salary,
       AVG(salary) OVER () AS company_avg
FROM employees;
name  | department  | salary | company_avg
Ana   | Engineering | 150000 | 115000.0
Ben   | Engineering | 150000 | 115000.0
Chloe | Engineering | 120000 | 115000.0
Dev   | Sales       | 90000  | 115000.0
Emma  | Sales       | 110000 | 115000.0
Felix | Sales       | 70000  | 115000.0

All six rows are still there, and each one carries the company-wide average. The syntax has three optional parts inside the parentheses:

function() OVER (
  PARTITION BY column     -- split rows into groups
  ORDER BY column         -- order rows within each group
  ROWS BETWEEN ... AND ...  -- which rows count (the frame)
)

Window function vs GROUP BY

This is the difference that matters most. GROUP BY returns one row per group:

SELECT department, AVG(salary) AS dept_avg
FROM employees
GROUP BY department;
department  | dept_avg
Engineering | 140000.0
Sales       | 90000.0

Six rows became two, and the names are gone. A window function keeps all six, which is how you can show each employee next to their department average.

PARTITION BY: calculate per group

PARTITION BY splits the rows into groups, and the function restarts for each one. It is like GROUP BY without the collapsing. See PARTITION BY in depth for more.

SELECT name, department, salary,
       AVG(salary) OVER (PARTITION BY department) AS dept_avg
FROM employees;
name  | department  | salary | dept_avg
Ana   | Engineering | 150000 | 140000.0
Ben   | Engineering | 150000 | 140000.0
Chloe | Engineering | 120000 | 140000.0
Dev   | Sales       | 90000  | 90000.0
Emma  | Sales       | 110000 | 90000.0
Felix | Sales       | 70000  | 90000.0

Dividing a row by its partition total gives each person's share of their department's payroll:

SELECT name, department, salary,
       ROUND(100.0 * salary / SUM(salary) OVER (PARTITION BY department), 1) AS pct_of_dept
FROM employees;
name  | department  | salary | pct_of_dept
Ana   | Engineering | 150000 | 35.7
Ben   | Engineering | 150000 | 35.7
Chloe | Engineering | 120000 | 28.6
Dev   | Sales       | 90000  | 33.3
Emma  | Sales       | 110000 | 40.7
Felix | Sales       | 70000  | 25.9

Ranking functions: ROW_NUMBER, RANK, DENSE_RANK, NTILE

Ranking functions need an ORDER BY inside OVER to know what "first" means. The three main ones differ only in how they treat ties. Ana and Ben both earn 150,000, so watch the results:

SELECT name, department, salary,
       ROW_NUMBER() OVER (ORDER BY salary DESC) AS row_num,
       RANK()       OVER (ORDER BY salary DESC) AS rnk,
       DENSE_RANK() OVER (ORDER BY salary DESC) AS dense_rnk
FROM employees;
name  | department  | salary | row_num | rnk | dense_rnk
Ana   | Engineering | 150000 | 1       | 1   | 1
Ben   | Engineering | 150000 | 2       | 1   | 1
Chloe | Engineering | 120000 | 3       | 3   | 2
Emma  | Sales       | 110000 | 4       | 4   | 3
Dev   | Sales       | 90000  | 5       | 5   | 4
Felix | Sales       | 70000  | 6       | 6   | 5
FunctionTiesGaps after a tie?Use it for
ROW_NUMBER()Broken arbitrarily (1, 2, 3)NoExactly one row per group, deduplication
RANK()Same rank (1, 1, 3)YesOlympic-style rankings
DENSE_RANK()Same rank (1, 1, 2)No"Second highest salary" questions

More detail in our guides to ROW_NUMBER() and DENSE_RANK().

Top row per group

The most common real-world use: the highest paid person in each department. You cannot filter on a window function in WHERE, so wrap the query in a subquery (or CTE) and filter outside. Adding name as a tiebreaker makes the result deterministic.

SELECT * FROM (
  SELECT name, department, salary,
         ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC, name) AS rn
  FROM employees
)
WHERE rn = 1;
name | department  | salary | rn
Ana  | Engineering | 150000 | 1
Emma | Sales       | 110000 | 1

Change rn = 1 to rn <= 3 for a top 3 per group.

NTILE: split rows into buckets

NTILE(n) divides the ordered rows into n roughly equal buckets, handy for quartiles or tiers.

SELECT name, salary, NTILE(3) OVER (ORDER BY salary DESC) AS tier
FROM employees;
name  | salary | tier
Ana   | 150000 | 1
Ben   | 150000 | 1
Chloe | 120000 | 2
Emma  | 110000 | 2
Dev   | 90000  | 3
Felix | 70000  | 3

Running totals with SUM() OVER

Any aggregate (SUM, AVG, COUNT, MIN, MAX) becomes a window function when you add OVER. Add ORDER BY and it accumulates row by row:

SELECT sale_date, amount,
       SUM(amount) OVER (ORDER BY sale_date) AS running_total
FROM sales;
sale_date  | amount | running_total
2026-01-01 | 100    | 100
2026-01-02 | 150    | 250
2026-01-03 | 90     | 340
2026-01-04 | 200    | 540
2026-01-05 | 120    | 660

Add PARTITION BY to restart the total per customer, per month or per category.

LAG and LEAD: look at the previous or next row

LAG(column) returns the value from the previous row in the window, LEAD(column) the next one. They are the standard way to calculate day-over-day or month-over-month change. Full walkthrough: LAG and LEAD in SQL.

SELECT sale_date, amount,
       LAG(amount) OVER (ORDER BY sale_date) AS prev_day,
       amount - LAG(amount) OVER (ORDER BY sale_date) AS change
FROM sales;
sale_date  | amount | prev_day | change
2026-01-01 | 100    | NULL     | NULL
2026-01-02 | 150    | 100      | 50
2026-01-03 | 90     | 150      | -60
2026-01-04 | 200    | 90       | 110
2026-01-05 | 120    | 200      | -80

The first row has no previous row, so you get NULL. Pass a default as the third argument, LAG(amount, 1, 0), if you prefer 0.

FIRST_VALUE and LAST_VALUE

FIRST_VALUE pulls a value from the first row of the window, for example the top earner's name next to everyone in their department:

SELECT name, department, salary,
       FIRST_VALUE(name) OVER (PARTITION BY department ORDER BY salary DESC, name) AS top_earner
FROM employees;
name  | department  | salary | top_earner
Ana   | Engineering | 150000 | Ana
Ben   | Engineering | 150000 | Ana
Chloe | Engineering | 120000 | Ana
Emma  | Sales       | 110000 | Emma
Dev   | Sales       | 90000  | Emma
Felix | Sales       | 70000  | Emma

Window frames and moving averages

The frame says which rows around the current row are included. It is the ROWS BETWEEN part of the clause. A 3-day moving average uses the current row and the two before it:

SELECT sale_date, amount,
       ROUND(AVG(amount) OVER (
         ORDER BY sale_date
         ROWS BETWEEN 2 PRECEDING AND CURRENT ROW), 1) AS moving_avg_3
FROM sales;
sale_date  | amount | moving_avg_3
2026-01-01 | 100    | 100.0
2026-01-02 | 150    | 125.0
2026-01-03 | 90     | 113.3
2026-01-04 | 200    | 146.7
2026-01-05 | 120    | 136.7

The first two rows average fewer than three values because there are not enough earlier rows. Useful frame boundaries are UNBOUNDED PRECEDING, n PRECEDING, CURRENT ROW, n FOLLOWING and UNBOUNDED FOLLOWING. For more on this pattern, see moving averages in SQL.

The default frame trap: ROWS vs RANGE

When you write ORDER BY inside OVER without a frame, SQL uses RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. With RANGE, rows that tie on the ORDER BY value are treated as one group and get the same total. Here two sales on different days both equal 100:

SELECT sale_date, amount,
       SUM(amount) OVER (ORDER BY amount) AS range_default,
       SUM(amount) OVER (ORDER BY amount
         ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS rows_frame
FROM (SELECT * FROM sales UNION ALL SELECT '2026-01-06', 100);
sale_date  | amount | range_default | rows_frame
2026-01-03 | 90     | 90            | 90
2026-01-01 | 100    | 290           | 190
2026-01-06 | 100    | 290           | 290
2026-01-05 | 120    | 410           | 410
2026-01-02 | 150    | 560           | 560
2026-01-04 | 200    | 760           | 760

The two 100s share a running total of 290 under the default, but accumulate one at a time under ROWS. If you want a strict row-by-row running total, order by a unique column or spell out ROWS. This also matters for LAST_VALUE, which with the default frame returns the current row's value rather than the last row of the partition.

Common mistakes

  • Filtering in WHERE. Window functions run after WHERE, GROUP BY and HAVING, so WHERE ROW_NUMBER() OVER (...) = 1 is an error. Use a subquery or CTE (or QUALIFY in Snowflake, BigQuery and DuckDB).
  • Forgetting ORDER BY. ROW_NUMBER(), LAG() and running totals are meaningless without an order. Always add one.
  • Non-deterministic ties. If two rows tie on the ORDER BY columns, ROW_NUMBER() picks between them arbitrarily. Add a unique tiebreaker column.
  • Confusing PARTITION BY with GROUP BY. PARTITION BY never reduces the row count.
  • Old database versions. MySQL before 8.0 and SQLite before 3.25 have no window functions.

Practice what you learned

Reading is not enough; try these on the sample tables above:

  1. Show each employee and how much they earn above or below their department average.
  2. Find the second highest salary in each department (hint: DENSE_RANK).
  3. Add a column with each sale's percentage of the total of all sales.
  4. Calculate the day-over-day percent change in sales.

New to SQL? Work through the free interactive lessons first, then try the SQL practice problems and the advanced SQL interview questions, which include many window function problems. Keep the SQL cheat sheet open for syntax.

FAQ

What are window functions in SQL?

Functions that compute a value over a set of rows related to the current row, defined by an OVER clause, while still returning every row. Examples are ROW_NUMBER, RANK, LAG and SUM() OVER.

What is the difference between a window function and an aggregate function?

A regular aggregate with GROUP BY returns one row per group. The same aggregate with OVER returns one value per input row, so detail is preserved.

What is the difference between RANK, DENSE_RANK and ROW_NUMBER?

ROW_NUMBER gives unique numbers, RANK gives tied rows the same number and skips the next ones (1, 1, 3), and DENSE_RANK gives ties the same number without skipping (1, 1, 2).

Can I use a window function in WHERE?

No. Compute it in a subquery or CTE, then filter in the outer query.

Are window functions slow?

They need to sort or partition the data, so on very large tables an index matching the PARTITION BY and ORDER BY columns helps. They are usually faster and simpler than the self-join alternatives.

Which databases support window functions?

PostgreSQL, SQL Server, Oracle, MySQL 8.0+, MariaDB 10.2+, SQLite 3.25+, Snowflake, BigQuery and most modern engines.