Window functions perform calculations across a set of rows that are related to the current row, but unlike aggregates they do not collapse the result set — every input row still appears in the output. The OVER clause controls how the window is defined and accepts three optional components: a PARTITION BY that divides the result into independent groups, an ORDER BY that defines the sequence within each partition, and a frame clause that further restricts which rows participate. A typical use case is computing a running total with SUM(amount) OVER (ORDER BY date), possibly inside a PARTITION BY to restart the running total for each group such as each customer.
Ranking and positional functions are among the most useful window features. ROW_NUMBER() assigns a unique sequential integer to each row in its partition with no ties, while RANK() and DENSE_RANK() handle ties differently: RANK leaves gaps after ties (1, 2, 2, 4) while DENSE_RANK does not (1, 2, 2, 3). NTILE(n) divides rows into n buckets as evenly as possible, useful for percentile analysis. For comparing adjacent rows, LAG(column, 1) retrieves the value from the previous row in the partition and LEAD(column, 1) retrieves the value from the next row — both returning NULL when there is no neighbor. FIRST_VALUE returns the value from the first row in the window frame, and the related PERCENT_RANK gives the relative rank as a fraction between 0 and 1.
Window frames add fine-grained control over exactly which rows are included in the calculation. The clause ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW defines a frame running from the start of the partition to the current row, while ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING creates a centered sliding window of three rows. The distinction between ROWS and RANGE is subtle but important: ROWS counts physical row positions, while RANGE treats rows with equal ORDER BY values as a single peer group, so RANGE-based frames include all rows tied on the ordering key even if that is more than the literal row count specified.