How do window functions work in SQL?

Window functions in SQL allow you to perform calculations across a set of table rows related to the current row, without collapsing the results into a single output row like traditional aggregate functions. They operate within a “window” of data defined by the OVER() clause, which specifies how to partition, order, and frame the rows for the calculation. Unlike regular aggregates (e.g., SUM() or AVG()), window functions retain individual rows while adding computed values, making them useful for tasks like rankings, running totals, or comparing rows within groups.

A window function has three key components: partitioning, ordering, and framing. The PARTITION BY clause divides rows into groups (similar to GROUP BY but without aggregation), and the calculation is performed within each partition. For example, ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) assigns a unique rank to employees within each department based on salary. The ORDER BY clause defines the sequence of rows within the partition, which is critical for functions like RANK() or cumulative sums. The frame clause (e.g., ROWS BETWEEN 3 PRECEDING AND CURRENT ROW) further narrows the subset of rows used in the calculation, enabling sliding window operations like moving averages.

Common use cases include calculating running totals with SUM(sales) OVER (ORDER BY date ROWS UNBOUNDED PRECEDING), comparing a row to its peers using LAG() or LEAD(), or assigning percentiles with NTILE(). For instance, to analyze monthly sales trends, you might partition by month and compute a cumulative sum within each partition. Window functions are efficient because they avoid self-joins or subqueries, but they require careful attention to framing and ordering for accurate results. Developers should note that not all databases support advanced framing options, so syntax may vary slightly across systems like PostgreSQL, MySQL, or SQL Server.