What a window function is: OVER and PARTITION BY
A window function calculates a value for each row from a set of related rows, its "window", which is defined by OVER (PARTITION BY … ORDER BY …). Unlike GROUP BY, it keeps every row in the result.
Why it matters
"Show each employee with their department's average salary next to them." GROUP BY gives you the averages but throws away the employees; joining the averages back is clumsy. A window function puts the group's number on every row in one step.
The idea
Add OVER (…) after a function and it becomes a window function. OVER () with nothing inside means "the window is every row". PARTITION BY dept_id splits the rows into windows, one per department, like GROUP BY — but the rows stay separate, and each row gets its own window's answer.
ORDER BY inside OVER sets an order within each window, which ranking and running totals need. Any aggregate (SUM, AVG, COUNT, MIN, MAX) can be used this way, plus special window-only functions such as ROW_NUMBER and LAG.
A class photo where each pupil holds a card. GROUP BY would replace each form group with one person holding the group's average. A window function keeps every pupil in the photo, and each one's card shows their own form group's average.
In the photo the card is written once. A window function is worked out in the SELECT step, after WHERE and GROUP BY — so WHERE can't filter on it. To filter on a window result, wrap the query in a CTE.
Syntax
function(...) OVER (
PARTITION BY group_column
ORDER BY sort_column
)How it works
- FROM and WHERE produce the rows: 7 employees with a department.
- PARTITION BY dept_id makes three windows: department 1 (4 people), 2 (2 people), 3 (1 person).
AVG(salary)is worked out over each window: 78750, 52500, 52000.- Each row gets the answer for its own window, and no rows are removed.
- OVER () with no partition makes one window of all rows:
COUNT(*) OVER ()is 8 on every row.
Key points
- A window function keeps every row; GROUP BY collapses rows.
- PARTITION BY = which rows share a window. No PARTITION BY = one window of all rows.
- ORDER BY inside OVER = the order within each window (needed for ranks, LAG and running totals).
- Window functions run in the SELECT step: you can't use them in WHERE, GROUP BY or HAVING. Wrap in a CTE to filter.
- Any aggregate becomes a window function when you add OVER.
Common mistakes
- Writing
WHERE ROW_NUMBER() OVER (…) = 1— not allowed. Filter in an outer query. - Adding GROUP BY when you wanted every row kept.
- Confusing the ORDER BY inside OVER (order within the window) with the query's ORDER BY (order of the output).
Not true: A window function can be used directly in WHERE to filter rows.
In fact: Window functions are worked out after WHERE has run. Compute the value in a subquery or a CTE, then filter in the outer query.
In the interview
A window function computes a value over a set of rows related to the current row. The OVER clause defines that set: PARTITION BY says which rows share the window, and ORDER BY orders them. Unlike GROUP BY, it doesn't collapse rows, so each row keeps its detail and gains the computed value. Because window functions are evaluated in the SELECT phase, I filter on them from an outer query or CTE.
Part of Unit 5: Window functions: ranking, running totals and comparing rows in the free SQL course.