SQL Window Functions Explained: OVER, PARTITION BY and Running Totals

Window functions are one of the most powerful features in SQL, and one of the most asked-about in interviews. They let you calculate across a set of rows (a total, an average, a rank, the previous value) while keeping every row in the result.

The problem they solve#

With GROUP BY, you get one row per group. You can see each department's average salary, but you lose the individual employees:

SELECT department, AVG(salary) AS avg_salary
FROM employees
GROUP BY department;

department  | avg_salary
------------+-----------
Engineering | 79500
Marketing   | 46000
Sales       | 56500

What if you want each employee next to their department's average? That is exactly what a window function does.

The OVER clause#

Add OVER (...) after an aggregate, and it stops collapsing rows. The empty OVER () means "the window is every row":

SELECT name, department, salary,
       SUM(salary) OVER () AS total_payroll
FROM employees;

name  | department  | salary | total_payroll
------+-------------+--------+--------------
Asha  | Sales       | 52000  | 318000
Ravi  | Engineering | 78000  | 318000
Meera | Engineering | 81000  | 318000
John  | Marketing   | 46000  | 318000
Priya | Sales       | 61000  | 318000

Every row is still here, and each has the total next to it. That makes per-row calculations easy, such as each person's share of the payroll:

SELECT name, salary,
       ROUND(100.0 * salary / SUM(salary) OVER (), 1) AS pct_of_payroll
FROM employees
ORDER BY salary DESC;

name  | salary | pct_of_payroll
------+--------+---------------
Meera | 81000  | 25.5
Ravi  | 78000  | 24.5
Priya | 61000  | 19.2
Asha  | 52000  | 16.4
John  | 46000  | 14.5

PARTITION BY: a window per group#

PARTITION BY splits the rows into groups, and the function is calculated separately within each one, like GROUP BY without collapsing:

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

name  | department  | salary | dept_avg
------+-------------+--------+---------
Meera | Engineering | 81000  | 79500
Ravi  | Engineering | 78000  | 79500
John  | Marketing   | 46000  | 46000
Priya | Sales       | 61000  | 56500
Asha  | Sales       | 52000  | 56500

More in PARTITION BY.

ORDER BY inside OVER: running totals#

Adding ORDER BY inside OVER makes the window grow row by row: each row sees itself and every row before it. With SUM, that is a running total:

SELECT month, region, revenue,
       SUM(revenue) OVER (PARTITION BY region ORDER BY month) AS running_total
FROM monthly_sales
ORDER BY region, month;

month   | region | revenue | running_total
--------+--------+---------+--------------
2026-01 | North  | 120     | 120
2026-02 | North  | 150     | 270
2026-03 | North  | 110     | 380
2026-04 | North  | 170     | 550
2026-01 | South  | 90      | 90
2026-02 | South  | 90      | 180
2026-03 | South  | 130     | 310
2026-04 | South  | 125     | 435

The running total restarts for each region because of PARTITION BY region.

Window frames: rolling calculations#

You can control exactly which rows the window contains with a frame. A rolling three-month total:

SELECT region, month, revenue,
       SUM(revenue) OVER (
         PARTITION BY region
         ORDER BY month
         ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
       ) AS rolling_3
FROM monthly_sales
ORDER BY region, month;

region | month   | revenue | rolling_3
-------+---------+---------+----------
North  | 2026-01 | 120     | 120
North  | 2026-02 | 150     | 270
North  | 2026-03 | 110     | 380
North  | 2026-04 | 170     | 430
South  | 2026-01 | 90      | 90
South  | 2026-02 | 90      | 180
South  | 2026-03 | 130     | 310
South  | 2026-04 | 125     | 345

In April, North's window holds February, March and April: 150 + 110 + 170 = 430. Rolling averages, used to smooth noisy data, work the same way with AVG.

The full syntax#

function(...) OVER (
  PARTITION BY columns      -- optional: separate windows per group
  ORDER BY columns          -- optional: order within each window
  ROWS BETWEEN ... AND ...  -- optional: which rows the window includes
)

Types of window functions#

Where window functions can be used#

Window functions are calculated after WHERE, GROUP BY and HAVING, just before ORDER BY. So you can use them in SELECT and ORDER BY, but not in WHERE. To filter on a window result (such as "rank = 1"), wrap the query in a subquery or CTE, as you will see in the next lessons.

Window functions are supported by PostgreSQL, SQL Server, Oracle, SQLite 3.25+, and MySQL 8.0+.

Common mistakes#

Practice#

SELECT name, department, salary,
       salary - AVG(salary) OVER (PARTITION BY department) AS vs_dept_avg
FROM employees
ORDER BY department, salary DESC;

name  | department  | salary | vs_dept_avg
------+-------------+--------+------------
Meera | Engineering | 81000  | 1500
Ravi  | Engineering | 78000  | -1500
John  | Marketing   | 46000  | 0
Priya | Sales       | 61000  | 4500
Asha  | Sales       | 52000  | -4500

Key takeaway#