What are window functions in SQL?

Window functions, also known as analytic functions, operate on a set of rows related to the current row and return a single value for each row in the result set. Unlike aggregate functions that collapse rows, window functions preserve the original row count. The syntax uses the OVER clause, which defines the window: a PARTITION BY clause to divide the data into groups, an ORDER BY clause to sort rows within each partition, and an optional frame specification (ROWS BETWEEN …) to limit the range of rows considered.

Common window functions include ROW_NUMBER(), RANK(), DENSE_RANK(), NTILE(), LAG(), LEAD(), SUM(), AVG(), MIN(), and MAX(). For example, to assign a sequential rank within each department:

SELECT employee_id, department, salary,

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

FROM employees;

This returns every employee with a rank that resets for each department. Window functions are powerful for running totals, moving averages, percentiles, and detecting changes between rows. They run in O(n) time for each partition, making them efficient for large datasets when used with proper indexing.