Window Functions — Introduction

A query technique that adds aggregated context to every row without collapsing them. The OVER() clause, partitions, ordering, framing, and the four categories of window functions you'll meet through this section.

A window function performs a calculation across a set of rows that are somehow related to the current row — but unlike a regular aggregate, it returns one value per row instead of collapsing the rows together. You get the per-row detail and the aggregated context side by side.

Want to show every employee's salary alongside their department's average? The total payroll? Their rank within the department? Window functions handle all of these in a single query, without subqueries or self-joins.

An aggregate hides the input rows behind a summary value. A window function lets you keep the rows and see the summary — every row carries its own copy of the relevant aggregate.

Any window function ends with an OVER() clause. The clause is what flips an aggregate-looking expression into a window operation.

CREATE TABLE employees (
    employee_id    INT,
    first_name     VARCHAR(50),
    last_name      VARCHAR(50),
    salary         DECIMAL(10, 2),
    manager_id     INT,
    department_id  INT
);

INSERT INTO employees VALUES
(200, 'Jennifer',  'Whalen',     4400.00, 101, 1),
(201, 'Michael',   'Hartstein', 13000.00, 100, 2),
(202, 'Pat',       'Fay',        6000.00, 201, 2),
(203, 'Susan',     'Mavris',     6500.00, 101, 4),
(204, 'Hermann',   'Baer',      10000.00, 101, 4),
(205, 'Shelley',   'Higgins',   12008.00, 101, 1),
(206, 'William',   'Gietz',      8300.00, 205, 1),
(100, 'Steven',    'King',      24000.00, NULL, 9),
(101, 'Neena',     'Kochhar',   17000.00, 100, 9),
(102, 'Lex',       'De Haan',   17000.00, 100, 9);

SELECT first_name,
       salary,
       AVG(salary) OVER () AS company_avg
FROM   employees;

Each row keeps its own salary and picks up the company-wide average. No GROUP BY, no subquery — the window is the whole table.

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

Now the average resets at every department. Row by row, you see "your salary, your department's average" — the comparison happens inline, no JOIN required.

SELECT first_name,
       salary,
       SUM(salary) OVER (ORDER BY employee_id) AS running_total
FROM   employees;

Adding ORDER BY turns the window into a running calculation — each row's SUM includes itself plus everything before it. That's the framing default kicking in.

When you add ORDER BY inside OVER(), the default frame is "every row from the start of the partition up to the current row" — that's why SUM(...) OVER (ORDER BY id) gives a running total. You can override this with ROWS or RANGE to look at, say, "the previous three rows" or "rows whose value is within 100 of mine":

-- 3-row trailing average
SELECT order_id, total,
       AVG(total) OVER (ORDER BY order_id
                        ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS rolling_avg
FROM   orders;

You can also keep the partition without any framing at all by leaving out ORDER BY — then every row sees the whole partition.

• In the SELECT list and ORDER BY clauses.
• Not in WHERE, GROUP BY, or HAVING — they're calculated after those clauses run. Use a subquery or CTE to filter on a window result.

-- Filter on a window result via a CTE
WITH ranked AS (
    SELECT first_name, salary,
           RANK() OVER (ORDER BY salary DESC) AS r
    FROM   employees
)
SELECT * FROM ranked WHERE r <= 3;

• Aggregate window functions — overview and the five flavours (SUM, AVG, COUNT, MAX, MIN).
• Ranking — RANK, DENSE_RANK, ROW_NUMBER for top-N reports.
• Distribution — PERCENT_RANK and CUME_DIST for percentile work.