SQL window functions — replacing self-joins and subqueries

Complex SQL queries use multiple self-joins or correlated subqueries to calculate running totals, rankings, or comparisons with previous/next rows. These queries are slow and hard to read.

Use window functions for row-relative calculations: (1) Ranking: ROW_NUMBER(), RANK(), DENSE_RANK() OVER (PARTITION BY group ORDER BY col). (2) Running total: SUM(amount) OVER (ORDER BY date ROWS UNBOUNDED PRECEDING). (3) Previous/next row: LAG(col, 1) OVER (ORDER BY date), LEAD(col, 1) OVER (ORDER BY date). (4) Moving average: AVG(value) OVER (ORDER BY date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW). (5) First/last in group: FIRST_VALUE(col) OVER (PARTITION BY group ORDER BY col). (6) Percentage of total: amount / SUM(amount) OVER () * 100. Window functions don't reduce rows (unlike GROUP BY) — they add computed columns.

Window functions operate on a set of rows related to the current row without collapsing them. They're computed after WHERE, GROUP BY, and HAVING but before ORDER BY and LIMIT, making them both powerful and efficient.