SQLsqlwindow-functionsanalyticsperformanceproduction-ready

Window Functions Patterns

Advanced SQL window functions for running totals, rankings, lag/lead analysis, and moving averages without self-joins.


title: "Window Functions Patterns" description: "Advanced SQL window functions for running totals, rankings, lag/lead analysis, and moving averages without self-joins." category: "SQL" language: "sql" tags: ["sql", "window-functions", "analytics", "performance", "production-ready"] createdAt: "2025-02-10" updatedAt: "2025-03-01"

Overview

Window functions enable complex analytics (running totals, rankings, period comparisons) in a single query pass without self-joins or subqueries. They operate on a "window" of rows relative to the current row.

Code

SUM(amount) OVER (PARTITION BY customer_id ORDER BY date) AS running_total
LAG(revenue) OVER (ORDER BY month) AS prev_month
DENSE_RANK() OVER (ORDER BY score DESC) AS rank

Notes

  • PARTITION BY creates independent windows per group (like GROUP BY for window functions)
  • ROWS BETWEEN defines the frame explicitly (UNBOUNDED, N PRECEDING, CURRENT ROW, N FOLLOWING)
  • LAG(col, n) and LEAD(col, n) access rows before/after without self-join
  • NTILE(4) divides results into equal quartiles for distribution analysis
  • Window functions execute after WHERE, GROUP BY, and HAVING but before ORDER BY and LIMIT