SQL practice › SQL reference › Window functions

SQL window functions: OVER, PARTITION BY and ORDER BY, with examples you can run

A window function computes a value for each row from a group of related rows, and keeps every row. AVG(salary) OVER (PARTITION BY department) puts the department's average salary beside each employee: nine employees in, nine rows out. The same AVG with GROUP BY department returns three rows, one per department, and the employees are gone.

What goes inside OVER (...) decides which rows each row looks at. PARTITION BY splits the rows into groups. ORDER BY puts them in order, for functions that count or look back, like ROW_NUMBER and LAG. One rule catches everyone: a window function can't be used in WHERE. You compute it in a CTE or a subquery and filter outside.

Skip to the 4 exercises ↓

Syntax

SELECT name, department, hire_date,
       ROW_NUMBER() OVER (PARTITION BY department ORDER BY hire_date) AS nth_hire
FROM employees
ROW_NUMBER()
The function. It can be a ranking function (ROW_NUMBER, RANK, DENSE_RANK), one that looks at another row (LAG, LEAD) or any aggregate (SUM, AVG, COUNT, MIN, MAX).
OVER (...)
What makes it a window function. With nothing inside, OVER (), the window is every row of the result.
PARTITION BY department
Optional. Splits the rows into groups, and the function starts over in each one. Unlike GROUP BY, the rows stay separate.
ORDER BY hire_date
The order of the rows inside each partition, for functions that count or look back. It doesn't sort the result: for that, add a normal ORDER BY at the end of the query.

The data

Nine employees in three departments: four in Engineering, three in Sales, two in Support. Most of the queries on this page run on this table.

employees
idnamedepartmentsalarymanager_idhire_date
1MayaSales72000NULL2019-03-11
2OmarSales5800012021-06-01
3LenaSales5800012022-02-14
4RajEngineering95000NULL2018-09-03
5ZoeEngineering8800042020-01-20
6IvanEngineering8100042021-11-08
7NoraSupport46000NULL2020-07-15
8TheoSupport4300072023-04-03
9KimEngineering8800042022-08-22

GROUP BY vs a window function: fold the rows or keep them

Both answer "what is the average salary of each department?". The difference is what happens to the rows. GROUP BY folds each department into a single row. A window function leaves every row where it is and writes the answer beside it. Run both.

Live queryGROUP BY: one row per department
Table: employees
employees
idnamedepartmentsalarymanager_idhire_date
1MayaSales72000NULL2019-03-11
2OmarSales5800012021-06-01
3LenaSales5800012022-02-14
4RajEngineering95000NULL2018-09-03
5ZoeEngineering8800042020-01-20
6IvanEngineering8100042021-11-08
7NoraSupport46000NULL2020-07-15
8TheoSupport4300072023-04-03
9KimEngineering8800042022-08-22
Ctrl/⌘ + Enter to run
Result · 3 rows
departmentavg_salary
Engineering88000
Sales62667
Support44500
Live queryOVER: every employee beside their department's average
Table: employees
employees
idnamedepartmentsalarymanager_idhire_date
1MayaSales72000NULL2019-03-11
2OmarSales5800012021-06-01
3LenaSales5800012022-02-14
4RajEngineering95000NULL2018-09-03
5ZoeEngineering8800042020-01-20
6IvanEngineering8100042021-11-08
7NoraSupport46000NULL2020-07-15
8TheoSupport4300072023-04-03
9KimEngineering8800042022-08-22
Ctrl/⌘ + Enter to run
Result · 9 rows
namedepartmentsalarydept_avg
RajEngineering9500088000
ZoeEngineering8800088000
IvanEngineering8100088000
KimEngineering8800088000
MayaSales7200062667
OmarSales5800062667
LenaSales5800062667
NoraSupport4600044500
TheoSupport4300044500

PARTITION BY and ORDER BY inside OVER

PARTITION BY is the window function's version of GROUP BY: it says which rows belong together. It doesn't fold them, so you never have to list the other columns the way GROUP BY makes you.

ORDER BY inside OVER gives those rows an order. Functions that count rows need one. ROW_NUMBER() numbers the rows of each partition 1, 2, 3 in that order.

Live queryROW_NUMBER: who was hired first, second, third in each department
Table: employees
employees
idnamedepartmentsalarymanager_idhire_date
1MayaSales72000NULL2019-03-11
2OmarSales5800012021-06-01
3LenaSales5800012022-02-14
4RajEngineering95000NULL2018-09-03
5ZoeEngineering8800042020-01-20
6IvanEngineering8100042021-11-08
7NoraSupport46000NULL2020-07-15
8TheoSupport4300072023-04-03
9KimEngineering8800042022-08-22
Ctrl/⌘ + Enter to run
Result · 9 rows
namedepartmenthire_datenth_hire
RajEngineering2018-09-031
ZoeEngineering2020-01-202
IvanEngineering2021-11-083
KimEngineering2022-08-224
MayaSales2019-03-111
OmarSales2021-06-012
LenaSales2022-02-143
NoraSupport2020-07-151
TheoSupport2023-04-032

With an aggregate, an ORDER BY inside OVER does something more: SUM(amount) OVER (ORDER BY order_date) adds up the rows so far instead of all of them. That is a running total, and it has its own page. For the three numbering functions and what each does with ties, see RANK vs DENSE_RANK vs ROW_NUMBER.

LAG: the previous row

LAG(x) returns the value of x on the row before the current one, in the window's order. It is how you compare a row with the one before it: this order with the previous order, this month with last month. The example uses a shop's orders.

orders
idcustomer_idproductamountorder_date
1011Desk2402026-01-05
1022Lamp352026-01-09
1031Chair1202026-01-17
1043Monitor1802026-02-02
1052Desk2402026-02-11
1064Lamp352026-02-20
1071Lamp352026-03-03
1083Chair1202026-03-14
Live queryEach order beside the amount of the order before it
Table: orders
orders
idcustomer_idproductamountorder_date
1011Desk2402026-01-05
1022Lamp352026-01-09
1031Chair1202026-01-17
1043Monitor1802026-02-02
1052Desk2402026-02-11
1064Lamp352026-02-20
1071Lamp352026-03-03
1083Chair1202026-03-14
Ctrl/⌘ + Enter to run
Result · 8 rows
idorder_dateamountprevious_amount
1012026-01-05240NULL
1022026-01-0935240
1032026-01-1712035
1042026-02-02180120
1052026-02-11240180
1062026-02-2035240
1072026-03-033535
1082026-03-1412035

Why a window function can't be used in WHERE

You numbered the rows, and now you want only number 1. The natural thing to write is WHERE ROW_NUMBER() OVER (...) = 1. It fails.

The reason is the order in which a query is processed. WHERE chooses the rows first. Window functions are computed afterwards, on the rows that are left (after GROUP BY and HAVING too). When WHERE runs, the number doesn't exist yet. Giving it an alias in SELECT and writing WHERE nth_hire = 1 fails for the same reason.

Live queryA window function in WHERE: an error
Table: employees
employees
idnamedepartmentsalarymanager_idhire_date
1MayaSales72000NULL2019-03-11
2OmarSales5800012021-06-01
3LenaSales5800012022-02-14
4RajEngineering95000NULL2018-09-03
5ZoeEngineering8800042020-01-20
6IvanEngineering8100042021-11-08
7NoraSupport46000NULL2020-07-15
8TheoSupport4300072023-04-03
9KimEngineering8800042022-08-22
Ctrl/⌘ + Enter to run

SQLite says: misuse of window function ROW_NUMBER()

Live queryThe fix: compute it in a CTE, filter outside
Table: employees
employees
idnamedepartmentsalarymanager_idhire_date
1MayaSales72000NULL2019-03-11
2OmarSales5800012021-06-01
3LenaSales5800012022-02-14
4RajEngineering95000NULL2018-09-03
5ZoeEngineering8800042020-01-20
6IvanEngineering8100042021-11-08
7NoraSupport46000NULL2020-07-15
8TheoSupport4300072023-04-03
9KimEngineering8800042022-08-22
Ctrl/⌘ + Enter to run
Result · 3 rows
namedepartmenthire_date
RajEngineering2018-09-03
MayaSales2019-03-11
NoraSupport2020-07-15

Window functions cheat sheet

FunctionWhat each row gets
ROW_NUMBER()Its position in the window's order: 1, 2, 3, 4. Never the same number twice.
RANK()Its position, with tied rows sharing a number and a gap after them: 1, 2, 2, 4.
DENSE_RANK()The same without the gap: 1, 2, 2, 3.
NTILE(n)A bucket number from 1 to n, with the rows split as evenly as possible.
LAG(x), LEAD(x)The value of x on the previous row, or the next one. NULL when there is no such row.
SUM(x), AVG(x), COUNT(*), MIN(x), MAX(x)The aggregate over the row's window. With an ORDER BY inside OVER: over the rows so far (a running total).
WindowThe rows each row looks at
OVER ()Every row of the result.
OVER (PARTITION BY a)The rows with the same a.
OVER (ORDER BY b)All rows, in the order of b. An aggregate then covers the rows from the first one up to the current row and any rows tied with it.
OVER (PARTITION BY a ORDER BY b)The rows with the same a, in the order of b.

Practice: 4 exercises

Write the query in the box and press Run. Your result is compared with the right one, on these tables and again on changed data, so a query with the values typed in won't pass. Column names don't matter; the columns and rows do.

Exercise 1Head count beside every employee

Return each employee's name, their department and the number of people in that department. One row per employee.

Table: employees
employees
idnamedepartmentsalarymanager_idhire_date
1MayaSales72000NULL2019-03-11
2OmarSales5800012021-06-01
3LenaSales5800012022-02-14
4RajEngineering95000NULL2018-09-03
5ZoeEngineering8800042020-01-20
6IvanEngineering8100042021-11-08
7NoraSupport46000NULL2020-07-15
8TheoSupport4300072023-04-03
9KimEngineering8800042022-08-22
Ctrl/⌘ + Enter to run
Show the answer
SELECT name, department,
       COUNT(*) OVER (PARTITION BY department)
FROM employees
Result · 9 rows
namedepartmentCOUNT(*) OVER (PARTITION BY department)
RajEngineering4
ZoeEngineering4
IvanEngineering4
KimEngineering4
MayaSales3
OmarSales3
LenaSales3
NoraSupport2
TheoSupport2

COUNT(*) is an ordinary aggregate, and OVER turns it into a window function. PARTITION BY department makes each row count the rows of its own department: 4 for Engineering, 3 for Sales, 2 for Support. With GROUP BY you would get those three numbers and lose the names.

Exercise 2Number the employees by seniority

Return each employee's name, their hire_date and their position in order of hiring across the whole company: 1 for the person hired first, 2 for the next, and so on. No two people share a hire date.

Table: employees
employees
idnamedepartmentsalarymanager_idhire_date
1MayaSales72000NULL2019-03-11
2OmarSales5800012021-06-01
3LenaSales5800012022-02-14
4RajEngineering95000NULL2018-09-03
5ZoeEngineering8800042020-01-20
6IvanEngineering8100042021-11-08
7NoraSupport46000NULL2020-07-15
8TheoSupport4300072023-04-03
9KimEngineering8800042022-08-22
Ctrl/⌘ + Enter to run
Show the answer
SELECT name, hire_date,
       ROW_NUMBER() OVER (ORDER BY hire_date)
FROM employees
Result · 9 rows
namehire_dateROW_NUMBER() OVER (ORDER BY hire_date)
Raj2018-09-031
Maya2019-03-112
Zoe2020-01-203
Nora2020-07-154
Omar2021-06-015
Ivan2021-11-086
Lena2022-02-147
Kim2022-08-228
Theo2023-04-039

ROW_NUMBER() numbers the rows in the order given inside OVER. There is no PARTITION BY, so all nine employees are one group: Raj is 1 (hired in 2018) and Theo is 9. The dates are all different, so RANK() would give the same numbers here.

Exercise 3The previous order's amount

For each order return its id, its amount and the amount of the order placed just before it, by order_date (no two orders share a date). The first order has no order before it: it should show NULL.

Table: orders
orders
idcustomer_idproductamountorder_date
1011Desk2402026-01-05
1022Lamp352026-01-09
1031Chair1202026-01-17
1043Monitor1802026-02-02
1052Desk2402026-02-11
1064Lamp352026-02-20
1071Lamp352026-03-03
1083Chair1202026-03-14
Ctrl/⌘ + Enter to run
Show the answer
SELECT id, amount,
       LAG(amount) OVER (ORDER BY order_date)
FROM orders
Result · 8 rows
idamountLAG(amount) OVER (ORDER BY order_date)
101240NULL
10235240
10312035
104180120
105240180
10635240
1073535
10812035

LAG(amount) reads amount from the previous row of the window, and ORDER BY order_date inside OVER is what defines "previous". Order 101 comes first, so it has nothing before it and gets NULL.

Exercise 4Who earns more than their department's average

Return the name, department and salary of every employee who earns more than the average salary of their own department.

Table: employees
employees
idnamedepartmentsalarymanager_idhire_date
1MayaSales72000NULL2019-03-11
2OmarSales5800012021-06-01
3LenaSales5800012022-02-14
4RajEngineering95000NULL2018-09-03
5ZoeEngineering8800042020-01-20
6IvanEngineering8100042021-11-08
7NoraSupport46000NULL2020-07-15
8TheoSupport4300072023-04-03
9KimEngineering8800042022-08-22
Ctrl/⌘ + Enter to run
Show the answer
WITH with_avg AS (
  SELECT name, department, salary,
         AVG(salary) OVER (PARTITION BY department) AS dept_avg
  FROM employees
)
SELECT name, department, salary
FROM with_avg
WHERE salary > dept_avg
Result · 3 rows
namedepartmentsalary
RajEngineering95000
MayaSales72000
NoraSupport46000

The window function puts each department's average beside every employee, but it can't be tested in the same query's WHERE. Inside a CTE it becomes a column, dept_avg, and the outer query filters on it. Raj, Maya and Nora are above their averages. Zoe and Kim earn exactly Engineering's average, 88000, so they are not. A correlated subquery in WHERE gets the same rows.

Questions people ask

What is the difference between GROUP BY and PARTITION BY?

Both put rows that share a value together. GROUP BY then returns one row per group, so only the grouped columns and aggregates can be selected. PARTITION BY lives inside OVER and returns every row, with the group's result written beside each one. Use GROUP BY when you want the summary, and a window function when you want the detail rows and the summary together.

Can I use a window function in WHERE or HAVING?

No. Window functions are allowed in the SELECT list and in ORDER BY, because they are computed after WHERE, GROUP BY and HAVING have done their work. To filter on one, compute it in a CTE or a subquery and put the condition in the outer query.

Does ORDER BY inside OVER sort the result?

No. It only tells the window function in which order to count or look back. The order of the rows in the result is set by the ORDER BY at the end of the query, and the two don't have to match.

Can an aggregate like SUM or AVG be a window function?

Yes. Any ordinary aggregate becomes a window function when OVER follows it: SUM(amount) OVER () is the grand total on every row, SUM(amount) OVER (PARTITION BY customer_id) the customer's total on every row. Without OVER it is a plain aggregate again and folds the rows.

What does ROWS BETWEEN do in a window function?

It sets the frame: which rows of the partition, counted from the current row, an aggregate covers. ROWS BETWEEN 2 PRECEDING AND CURRENT ROW is the current row and the two before it, which gives a moving average. The running total page has live examples, and shows what the default frame does when two rows tie.

Keep going

Ten questions on a real database, from SELECT to JOINs and subqueries. Free, no sign-up.

Related

RANK vs DENSE_RANK vs ROW_NUMBER, side by sideSQL running total with SUM() OVERSQL top N per group, with live queriesSQL GROUP BY, with live queriesSQL subquery vs CTE: the same question three waysSQL interview questions, with answersSQL reference with queries you can runSQL practice online

Written and tested by Nico, who builds SheetDrill (about). Every query on this page was run on SQLite, and every exercise checked against right and wrong answers, before the page was published. Other databases differ in places; where they do, the page says so. Checked against: SQLite documentation: Window Functions, PostgreSQL documentation: Window Functions (tutorial), PostgreSQL documentation: Window Functions (function list). Last checked November 2026.