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 rankNotes
PARTITION BYcreates independent windows per group (like GROUP BY for window functions)ROWS BETWEENdefines the frame explicitly (UNBOUNDED, N PRECEDING, CURRENT ROW, N FOLLOWING)LAG(col, n)andLEAD(col, n)access rows before/after without self-joinNTILE(4)divides results into equal quartiles for distribution analysis- Window functions execute after WHERE, GROUP BY, and HAVING but before ORDER BY and LIMIT