Window Functions & OVER()

Suppose the team analyst wants a sheet where every innings is listed and next to it the team's total, the player's rank and the running total. GROUP BY cannot do this — it squashes the rows. Window functions calculate aggregates, ranks and comparisons while keeping every row.

Definition

A window function performs a calculation across a set of rows that are related to the current row — called the window — and returns a value for every row, without collapsing the rows into groups. A function becomes a window function when it is followed by the OVER() clause.

• Related to the current row — e.g. "all innings of the same player" or "all rows up to this date".
• The window — that set of rows. It is defined inside OVER( ... ).
• For every row — 12 rows in, 12 rows out. The result is added as an extra column.
• OVER() — the keyword that turns SUM(runs) (an aggregate) into SUM(runs) OVER (...) (a window function).

GROUP BY vs window functions

Same data, both ways (4 innings of MI and RCB):

-- GROUP BY: 4 rows → 2 rows
SELECT team, SUM(runs) AS total
FROM innings
WHERE id IN (1, 2, 4, 5)
GROUP BY team;

-- WINDOW: 4 rows → 4 rows, total repeated on each row
SELECT id, team, runs,
    SUM(runs) OVER (PARTITION BY team) AS team_total
FROM innings
WHERE id IN (1, 2, 4, 5);

Syntax — the parts of a window function

SUM(runs) OVER (
    PARTITION BY player
    ORDER BY match_date
    ROWS UNBOUNDED PRECEDING
)

* ORDER BY is required for ranking and value functions (see the table below).

OVER() — empty — means the window is the ENTIRE result set. Only the function and OVER are required; everything inside the brackets is optional.

Example — OVER() on the whole result set

SELECT id, player, runs,
    SUM(runs) OVER () AS grand_total
FROM innings;

How it works: (1) empty OVER() = one big window containing all 12 rows; (2) the SUM of that window (645 — NULL ignored) is repeated on every row, and all rows are kept. Even Dhoni's NULL row gets 645.

The three families of window functions

Which function allows which clause?

Window function rules

1. Where allowed: window functions can be used only in SELECT and ORDER BY. Not in WHERE, GROUP BY or HAVING.
2. No nesting: a window function cannot be placed inside another window function — AVG(SUM(runs) OVER ()) OVER () is an error.
3. Run after WHERE: SQL filters rows with WHERE first, then calculates the window over the rows that are left.
4. Filter on a window result: wrap the query in a subquery or CTE and filter in the outer query (WHERE rn = 1).

-- ❌ Error: windowed functions can only appear in the SELECT or ORDER BY clauses
SELECT id, runs
FROM innings
WHERE ROW_NUMBER() OVER (ORDER BY runs DESC) = 1;

-- ✅ Compute it in a subquery, filter outside
SELECT *
FROM (
    SELECT id, runs, ROW_NUMBER() OVER (ORDER BY runs DESC) AS rn
    FROM innings
) t
WHERE rn = 1;

Note: an aggregate inside a window function is fine when the query uses GROUP BY: SUM(SUM(runs)) OVER () = "sum of the group totals". That is not nesting two window functions.

Common mistakes

• ❌ Expecting SUM(runs) OVER () to reduce the row count → ✅ window functions never remove rows.
• ❌ Putting a window function in WHERE → ✅ use a subquery/CTE.
• ❌ RANK() OVER () without ORDER BY → ✅ ranking needs OVER (ORDER BY …).
• ❌ COUNT(DISTINCT player) OVER () → ✅ SQL Server does not allow DISTINCT inside a window aggregate.

Interview tip

Q: Can you use a window function in a WHERE clause? A: No. Window functions are evaluated after WHERE (and after GROUP BY/HAVING), at the SELECT step. To filter on one, calculate it in a subquery or CTE and filter in the outer query.