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.
Syntax
SELECT name, department, hire_date,
ROW_NUMBER() OVER (PARTITION BY department ORDER BY hire_date) AS nth_hire
FROM employeesROW_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 BYat 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.
| id | name | department | salary | manager_id | hire_date |
|---|---|---|---|---|---|
| 1 | Maya | Sales | 72000 | NULL | 2019-03-11 |
| 2 | Omar | Sales | 58000 | 1 | 2021-06-01 |
| 3 | Lena | Sales | 58000 | 1 | 2022-02-14 |
| 4 | Raj | Engineering | 95000 | NULL | 2018-09-03 |
| 5 | Zoe | Engineering | 88000 | 4 | 2020-01-20 |
| 6 | Ivan | Engineering | 81000 | 4 | 2021-11-08 |
| 7 | Nora | Support | 46000 | NULL | 2020-07-15 |
| 8 | Theo | Support | 43000 | 7 | 2023-04-03 |
| 9 | Kim | Engineering | 88000 | 4 | 2022-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.
Table: employees
| id | name | department | salary | manager_id | hire_date |
|---|---|---|---|---|---|
| 1 | Maya | Sales | 72000 | NULL | 2019-03-11 |
| 2 | Omar | Sales | 58000 | 1 | 2021-06-01 |
| 3 | Lena | Sales | 58000 | 1 | 2022-02-14 |
| 4 | Raj | Engineering | 95000 | NULL | 2018-09-03 |
| 5 | Zoe | Engineering | 88000 | 4 | 2020-01-20 |
| 6 | Ivan | Engineering | 81000 | 4 | 2021-11-08 |
| 7 | Nora | Support | 46000 | NULL | 2020-07-15 |
| 8 | Theo | Support | 43000 | 7 | 2023-04-03 |
| 9 | Kim | Engineering | 88000 | 4 | 2022-08-22 |
| department | avg_salary |
|---|---|
| Engineering | 88000 |
| Sales | 62667 |
| Support | 44500 |
Table: employees
| id | name | department | salary | manager_id | hire_date |
|---|---|---|---|---|---|
| 1 | Maya | Sales | 72000 | NULL | 2019-03-11 |
| 2 | Omar | Sales | 58000 | 1 | 2021-06-01 |
| 3 | Lena | Sales | 58000 | 1 | 2022-02-14 |
| 4 | Raj | Engineering | 95000 | NULL | 2018-09-03 |
| 5 | Zoe | Engineering | 88000 | 4 | 2020-01-20 |
| 6 | Ivan | Engineering | 81000 | 4 | 2021-11-08 |
| 7 | Nora | Support | 46000 | NULL | 2020-07-15 |
| 8 | Theo | Support | 43000 | 7 | 2023-04-03 |
| 9 | Kim | Engineering | 88000 | 4 | 2022-08-22 |
| name | department | salary | dept_avg |
|---|---|---|---|
| Raj | Engineering | 95000 | 88000 |
| Zoe | Engineering | 88000 | 88000 |
| Ivan | Engineering | 81000 | 88000 |
| Kim | Engineering | 88000 | 88000 |
| Maya | Sales | 72000 | 62667 |
| Omar | Sales | 58000 | 62667 |
| Lena | Sales | 58000 | 62667 |
| Nora | Support | 46000 | 44500 |
| Theo | Support | 43000 | 44500 |
PARTITION BY department so it reads OVER () and run it again: every row gets the company's average, 69889.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.
Table: employees
| id | name | department | salary | manager_id | hire_date |
|---|---|---|---|---|---|
| 1 | Maya | Sales | 72000 | NULL | 2019-03-11 |
| 2 | Omar | Sales | 58000 | 1 | 2021-06-01 |
| 3 | Lena | Sales | 58000 | 1 | 2022-02-14 |
| 4 | Raj | Engineering | 95000 | NULL | 2018-09-03 |
| 5 | Zoe | Engineering | 88000 | 4 | 2020-01-20 |
| 6 | Ivan | Engineering | 81000 | 4 | 2021-11-08 |
| 7 | Nora | Support | 46000 | NULL | 2020-07-15 |
| 8 | Theo | Support | 43000 | 7 | 2023-04-03 |
| 9 | Kim | Engineering | 88000 | 4 | 2022-08-22 |
| name | department | hire_date | nth_hire |
|---|---|---|---|
| Raj | Engineering | 2018-09-03 | 1 |
| Zoe | Engineering | 2020-01-20 | 2 |
| Ivan | Engineering | 2021-11-08 | 3 |
| Kim | Engineering | 2022-08-22 | 4 |
| Maya | Sales | 2019-03-11 | 1 |
| Omar | Sales | 2021-06-01 | 2 |
| Lena | Sales | 2022-02-14 | 3 |
| Nora | Support | 2020-07-15 | 1 |
| Theo | Support | 2023-04-03 | 2 |
PARTITION BY department and the numbers run from 1 to 9 across the company: Raj 1, Maya 2, Zoe 3.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.
| id | customer_id | product | amount | order_date |
|---|---|---|---|---|
| 101 | 1 | Desk | 240 | 2026-01-05 |
| 102 | 2 | Lamp | 35 | 2026-01-09 |
| 103 | 1 | Chair | 120 | 2026-01-17 |
| 104 | 3 | Monitor | 180 | 2026-02-02 |
| 105 | 2 | Desk | 240 | 2026-02-11 |
| 106 | 4 | Lamp | 35 | 2026-02-20 |
| 107 | 1 | Lamp | 35 | 2026-03-03 |
| 108 | 3 | Chair | 120 | 2026-03-14 |
Table: orders
| id | customer_id | product | amount | order_date |
|---|---|---|---|---|
| 101 | 1 | Desk | 240 | 2026-01-05 |
| 102 | 2 | Lamp | 35 | 2026-01-09 |
| 103 | 1 | Chair | 120 | 2026-01-17 |
| 104 | 3 | Monitor | 180 | 2026-02-02 |
| 105 | 2 | Desk | 240 | 2026-02-11 |
| 106 | 4 | Lamp | 35 | 2026-02-20 |
| 107 | 1 | Lamp | 35 | 2026-03-03 |
| 108 | 3 | Chair | 120 | 2026-03-14 |
| id | order_date | amount | previous_amount |
|---|---|---|---|
| 101 | 2026-01-05 | 240 | NULL |
| 102 | 2026-01-09 | 35 | 240 |
| 103 | 2026-01-17 | 120 | 35 |
| 104 | 2026-02-02 | 180 | 120 |
| 105 | 2026-02-11 | 240 | 180 |
| 106 | 2026-02-20 | 35 | 240 |
| 107 | 2026-03-03 | 35 | 35 |
| 108 | 2026-03-14 | 120 | 35 |
amount - LAG(amount) OVER (ORDER BY order_date) gives -205 for order 102. LEAD is the same function looking at the next row.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.
Table: employees
| id | name | department | salary | manager_id | hire_date |
|---|---|---|---|---|---|
| 1 | Maya | Sales | 72000 | NULL | 2019-03-11 |
| 2 | Omar | Sales | 58000 | 1 | 2021-06-01 |
| 3 | Lena | Sales | 58000 | 1 | 2022-02-14 |
| 4 | Raj | Engineering | 95000 | NULL | 2018-09-03 |
| 5 | Zoe | Engineering | 88000 | 4 | 2020-01-20 |
| 6 | Ivan | Engineering | 81000 | 4 | 2021-11-08 |
| 7 | Nora | Support | 46000 | NULL | 2020-07-15 |
| 8 | Theo | Support | 43000 | 7 | 2023-04-03 |
| 9 | Kim | Engineering | 88000 | 4 | 2022-08-22 |
SQLite says: misuse of window function ROW_NUMBER()
WHERE, GROUP BY and HAVING as well. The fix is the next query: compute the number first, filter after.Table: employees
| id | name | department | salary | manager_id | hire_date |
|---|---|---|---|---|---|
| 1 | Maya | Sales | 72000 | NULL | 2019-03-11 |
| 2 | Omar | Sales | 58000 | 1 | 2021-06-01 |
| 3 | Lena | Sales | 58000 | 1 | 2022-02-14 |
| 4 | Raj | Engineering | 95000 | NULL | 2018-09-03 |
| 5 | Zoe | Engineering | 88000 | 4 | 2020-01-20 |
| 6 | Ivan | Engineering | 81000 | 4 | 2021-11-08 |
| 7 | Nora | Support | 46000 | NULL | 2020-07-15 |
| 8 | Theo | Support | 43000 | 7 | 2023-04-03 |
| 9 | Kim | Engineering | 88000 | 4 | 2022-08-22 |
| name | department | hire_date |
|---|---|---|
| Raj | Engineering | 2018-09-03 |
| Maya | Sales | 2019-03-11 |
| Nora | Support | 2020-07-15 |
nth_hire is an ordinary column that WHERE can test. A subquery in FROM does the same job.Window functions cheat sheet
| Function | What 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). |
| Window | The 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.
Return each employee's name, their department and the number of people in that department. One row per employee.
Table: employees
| id | name | department | salary | manager_id | hire_date |
|---|---|---|---|---|---|
| 1 | Maya | Sales | 72000 | NULL | 2019-03-11 |
| 2 | Omar | Sales | 58000 | 1 | 2021-06-01 |
| 3 | Lena | Sales | 58000 | 1 | 2022-02-14 |
| 4 | Raj | Engineering | 95000 | NULL | 2018-09-03 |
| 5 | Zoe | Engineering | 88000 | 4 | 2020-01-20 |
| 6 | Ivan | Engineering | 81000 | 4 | 2021-11-08 |
| 7 | Nora | Support | 46000 | NULL | 2020-07-15 |
| 8 | Theo | Support | 43000 | 7 | 2023-04-03 |
| 9 | Kim | Engineering | 88000 | 4 | 2022-08-22 |
Show the answer
SELECT name, department,
COUNT(*) OVER (PARTITION BY department)
FROM employees| name | department | COUNT(*) OVER (PARTITION BY department) |
|---|---|---|
| Raj | Engineering | 4 |
| Zoe | Engineering | 4 |
| Ivan | Engineering | 4 |
| Kim | Engineering | 4 |
| Maya | Sales | 3 |
| Omar | Sales | 3 |
| Lena | Sales | 3 |
| Nora | Support | 2 |
| Theo | Support | 2 |
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.
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
| id | name | department | salary | manager_id | hire_date |
|---|---|---|---|---|---|
| 1 | Maya | Sales | 72000 | NULL | 2019-03-11 |
| 2 | Omar | Sales | 58000 | 1 | 2021-06-01 |
| 3 | Lena | Sales | 58000 | 1 | 2022-02-14 |
| 4 | Raj | Engineering | 95000 | NULL | 2018-09-03 |
| 5 | Zoe | Engineering | 88000 | 4 | 2020-01-20 |
| 6 | Ivan | Engineering | 81000 | 4 | 2021-11-08 |
| 7 | Nora | Support | 46000 | NULL | 2020-07-15 |
| 8 | Theo | Support | 43000 | 7 | 2023-04-03 |
| 9 | Kim | Engineering | 88000 | 4 | 2022-08-22 |
Show the answer
SELECT name, hire_date,
ROW_NUMBER() OVER (ORDER BY hire_date)
FROM employees| name | hire_date | ROW_NUMBER() OVER (ORDER BY hire_date) |
|---|---|---|
| Raj | 2018-09-03 | 1 |
| Maya | 2019-03-11 | 2 |
| Zoe | 2020-01-20 | 3 |
| Nora | 2020-07-15 | 4 |
| Omar | 2021-06-01 | 5 |
| Ivan | 2021-11-08 | 6 |
| Lena | 2022-02-14 | 7 |
| Kim | 2022-08-22 | 8 |
| Theo | 2023-04-03 | 9 |
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.
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
| id | customer_id | product | amount | order_date |
|---|---|---|---|---|
| 101 | 1 | Desk | 240 | 2026-01-05 |
| 102 | 2 | Lamp | 35 | 2026-01-09 |
| 103 | 1 | Chair | 120 | 2026-01-17 |
| 104 | 3 | Monitor | 180 | 2026-02-02 |
| 105 | 2 | Desk | 240 | 2026-02-11 |
| 106 | 4 | Lamp | 35 | 2026-02-20 |
| 107 | 1 | Lamp | 35 | 2026-03-03 |
| 108 | 3 | Chair | 120 | 2026-03-14 |
Show the answer
SELECT id, amount,
LAG(amount) OVER (ORDER BY order_date)
FROM orders| id | amount | LAG(amount) OVER (ORDER BY order_date) |
|---|---|---|
| 101 | 240 | NULL |
| 102 | 35 | 240 |
| 103 | 120 | 35 |
| 104 | 180 | 120 |
| 105 | 240 | 180 |
| 106 | 35 | 240 |
| 107 | 35 | 35 |
| 108 | 120 | 35 |
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.
Return the name, department and salary of every employee who earns more than the average salary of their own department.
Table: employees
| id | name | department | salary | manager_id | hire_date |
|---|---|---|---|---|---|
| 1 | Maya | Sales | 72000 | NULL | 2019-03-11 |
| 2 | Omar | Sales | 58000 | 1 | 2021-06-01 |
| 3 | Lena | Sales | 58000 | 1 | 2022-02-14 |
| 4 | Raj | Engineering | 95000 | NULL | 2018-09-03 |
| 5 | Zoe | Engineering | 88000 | 4 | 2020-01-20 |
| 6 | Ivan | Engineering | 81000 | 4 | 2021-11-08 |
| 7 | Nora | Support | 46000 | NULL | 2020-07-15 |
| 8 | Theo | Support | 43000 | 7 | 2023-04-03 |
| 9 | Kim | Engineering | 88000 | 4 | 2022-08-22 |
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| name | department | salary |
|---|---|---|
| Raj | Engineering | 95000 |
| Maya | Sales | 72000 |
| Nora | Support | 46000 |
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.