Window Functions Basics

Aggregates are powerful until you need the detail and the summary at the same time. GROUP BY collapses rows into one per group, so you can see the department average or the total budget, but you lose the individual names and salaries behind those numbers. A window function solves that tension. It calculates an aggregate — a sum, an average, a count — but returns it on every row instead of rolling them up. You get the big picture and the fine detail in one query. In this lesson you will compute running totals across the whole company, average salaries within each department, and compare each employee to their own team — all without losing a single row.

flowchart TD
  A["All rows"] --> G["GROUP BY dept"]
  G --> C["One row per dept"]
  A --> W["Window OVER (PARTITION BY dept)"]
  W --> R["Every row + dept avg"]
  style C fill:#b45309,color:#fff
  style R fill:#0e7490,color:#fff

GROUP BY collapses rows; a window function preserves every row and repeats the aggregate.

The OVER clause: turning an aggregate into a window

The only new syntax you need is the OVER clause. Take an ordinary aggregate like SUM(salary) and add OVER () after it. The result is a window function that repeats the grand total on every row:

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

The empty parentheses mean 'use the entire result set as the window.' Every row sees the same number. This is useful for percentages — salary * 100.0 / SUM(salary) OVER () shows each person's share of the payroll — but it is only the beginning.

Add ORDER BY inside OVER and the aggregate becomes a running total. The database sorts the rows, then accumulates the sum step by step. Row three shows the total of rows one through three; row ten shows the total of rows one through ten.

A running total of salaries ordered by employee id.

SELECT id, name, salary,
  SUM(salary) OVER (ORDER BY id) AS running_total
FROM employees;

PARTITION BY: reset the calculation per group

A running total across the whole company is interesting, but managers usually care about their own department. PARTITION BY inside OVER splits the data into independent groups — one window per department — and runs the calculation separately inside each one.

SELECT name, department_id, salary,
  AVG(salary) OVER (PARTITION BY department_id) AS dept_avg
FROM employees;

Every employee now sees the average salary of their own department, not the company as a whole. Engineers compare themselves to other engineers; marketers to marketers. The rows are not collapsed; every employee is still listed individually, but each carries a summary statistic from their partition.

You can combine PARTITION BY and ORDER BY in the same OVER clause. That gives you a running total that resets at the start of each department — a powerful pattern for financial reports and leaderboards.

flowchart LR
  D1["Dept 1 rows"] --> P1["Window 1<br/>avg = 825000"]
  D2["Dept 2 rows"] --> P2["Window 2<br/>avg = 575000"]
  style P1 fill:#0e7490,color:#fff
  style P2 fill:#1e293b,color:#fff

PARTITION BY creates a separate window for each department.

Average salary within each department, repeated on every row.

SELECT name, department_id, salary,
  AVG(salary) OVER (PARTITION BY department_id) AS dept_avg
FROM employees;

Window vs GROUP BY: when to use which

It is easy to confuse window functions with GROUP BY because both use aggregates. The difference is in what happens to the rows. GROUP BY collapses them; a window function preserves them. If the question is 'what is the average salary per department?' and you only need one row per department, GROUP BY is simpler and faster. If the question is 'show me every employee and how their pay compares to their department average?' you need the detail rows, so a window function is the right tool.

Another way to think about it: GROUP BY answers a question about the group; a window function attaches a group statistic to each member. Both are correct; they just serve different reports. Real analysts use both in the same query — GROUP BY for summary tables, windows for detail lists with context.

flowchart LR
  R1["Row 1: 720000"] --> S1["Running: 720000"]
  R2["Row 2: 690000"] --> S2["Running: 1410000"]
  R3["Row 3: 910000"] --> S3["Running: 2320000"]
  style S3 fill:#0e7490,color:#fff

ORDER BY inside OVER turns a static total into a running total.

Count how many people share each department, on every row.

SELECT name, department_id,
  COUNT(*) OVER (PARTITION BY department_id) AS dept_size
FROM employees;

Practice: windows in action

The exercises below start with a bare OVER clause, move into partitioned averages, then ask you to convert a broken GROUP BY query into a proper window function. The transfer task combines a window with arithmetic so you can see how each individual compares to their team average. Each query keeps every row intact, so compare the result counts to the employee table and confirm nothing was collapsed.

Exercise

Return name, salary, and a column called running_total that shows the cumulative sum of salaries when ordered by employee id.

SELECT name, salary,
  -- add a running total here
  AS running_total
FROM employees;

SELECT name, salary, SUM(salary) OVER (ORDER BY id) AS running_total FROM employees;

Exercise

Return name, department_id, salary, and a column called dept_avg that shows the average salary within that employee's department. Keep every employee as a separate row.

SELECT name, department_id, salary,
  -- add the department average here
  AS dept_avg
FROM employees;

SELECT name, department_id, salary, AVG(salary) OVER (PARTITION BY department_id) AS dept_avg FROM employees;

Exercise

This query tries to show each employee's salary alongside their department average, but it uses GROUP BY and collapses all employees into one row per department. Rewrite it using a window function so every employee appears on their own row with the department average.

SELECT department_id, AVG(salary) AS dept_avg
FROM employees
GROUP BY department_id;

SELECT name, department_id, salary, AVG(salary) OVER (PARTITION BY department_id) AS dept_avg FROM employees;

Exercise

Show each employee's name, salary, and the difference between their salary and their department's average. Call the difference gap. Use a window function so every employee keeps their own row.

SELECT name, salary,
  -- compute salary minus department average
  AS gap
FROM employees;

SELECT name, salary, salary - AVG(salary) OVER (PARTITION BY department_id) AS gap FROM employees;

Exercise

What does SUM(salary) OVER () compute?

Recap

Window functions compute aggregates without collapsing rows — every row stays visible.
OVER () uses the whole result set as the window.
OVER (PARTITION BY column) resets the calculation for each group of rows.
OVER (ORDER BY column) turns a total into a running total up to the current row.
Use window functions when you need both detail and summary in the same result set.

Next you will rank rows within partitions and compare each row to its neighbours with LEAD and LAG.

The OVER clause: turning an aggregate into a window

PARTITION BY: reset the calculation per group

Window vs GROUP BY: when to use which

Practice: windows in action

Recap

Checkpoint quiz

Go deeper — technical resources