SQL Window Functions Reference

Window functions are one of the most powerful features in SQL, letting you compute running totals, rankings, and period-over-period comparisons without subqueries or self-joins. Every SQL window function follows the same pattern: function_name() OVER (PARTITION BY ... ORDER BY ... ROWS/RANGE ...).

Step 1: Understand the OVER() Clause

The OVER() clause defines the window of rows. An empty OVER() means the entire result set. PARTITION BY department restarts the calculation for each department. ORDER BY salary DESC determines row order within each partition.

Step 2: Choose the Right Ranking Function

Use ROW_NUMBER() for a unique sequential number with no ties. Use RANK() when ties should skip numbers (1,1,3). Use DENSE_RANK() when ties should not skip numbers (1,1,2). Use NTILE(n) to divide rows into n equal buckets.

Step 3: Use LAG/LEAD for Period Comparisons

LAG(amount, 1) gives the previous row's value, perfect for month-over-month change: amount - LAG(amount, 1) OVER (ORDER BY month). LEAD(amount, 1) gives the next row's value. Both accept an optional default for when no previous/next row exists.

Step 4: Build Running Totals with ROWS BETWEEN

To compute a running total, add ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW to a SUM: SUM(amount) OVER (ORDER BY date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW). For a 7-day moving average: AVG(amount) OVER (ORDER BY date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW).