Window Functions

Per-row calculations across a related set of rows — running totals, rankings, and row-over-row comparisons — without collapsing rows like GROUP BY does.

Overview Q&A MCQ

Study first: Aggregations & GROUP BY

What it is

A window function computes a value for each row using a "window" of related rows (defined by OVER (...)), without collapsing them into one row per group the way GROUP BY does — every input row still appears in the output.

Core syntax

RANK() OVER (PARTITION BY department ORDER BY salary DESC)

PARTITION BY defines the window (like a virtual GROUP BY), ORDER BY defines the row's position within it.

Common functions

Ranking: ROW_NUMBER(), RANK(), DENSE_RANK() — differ in how they handle ties.
Offset: LAG()/LEAD() — read a preceding/following row's value, useful for period-over-period comparisons.
Running aggregates: SUM(...) OVER (ORDER BY date) gives a running total instead of one grand total.

Why GROUP BY comes first

Window functions are best understood as "GROUP BY's aggregation, but keeping every row" — the mental model of partitioning rows is shared, window functions just don't collapse the partition.