Glossary · Database

Window Function

A SQL function that performs calculations across a set of rows related to the current row without collapsing them.

Window functions (also called analytic functions) perform calculations across a set of table rows that are somehow related to the current row–unlike aggregate functions which collapse rows into a single result, window functions retain all rows in the output. Common window functions include ROW_NUMBER() (assigns sequential numbers), RANK() and DENSE_RANK() (ranking with and without gaps), LAG() and LEAD() (access previous/next row values), SUM() OVER (running totals), and AVG() OVER (moving averages). Window functions use the OVER clause to define the window (PARTITION BY for grouping, ORDER BY for ordering, and ROWS/RANGE for frame boundaries). They are essential for time-series analysis, leaderboards, percentile calculations, and year-over-year comparisons.

Fig — every answer ships with the tables, rows and SQL behind it.