Skip to content
Quantoros

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

Related questions