What is a window function?
SQLLevel: IntermediateWindow Functions
What is a window function?
Hints
- Which rows belong to the partition and frame for each current row?
- Check ordering, ties, frame boundaries and the difference from GROUP BY.
Answer
A window function performs a calculation across a set of rows that are related to the current row, without collapsing those rows into a single output row (unlike aggregate functions with GROUP BY).
The key part is OVER (...) — this defines the "window" of rows the function operates on.
SELECT
name,
department,
salary,
AVG(salary) OVER (PARTITION BY department) AS dept_avg,
salary - AVG(salary) OVER (PARTITION BY department) AS diff_from_dept_avg
FROM employees;
Every input row produces an output row. You get per-row results that incorporate information from the window of related rows.
Source and licence: SQL Interview Questions — Arda Kocadoru · MIT