SQL Window Functions: The Analytics Powerhouse

Window functions perform calculations across a set of rows related to the current row — without grouping them into a single output row. They're the key to analytics queries: rankings, running totals, period-over-period comparisons, and moving averages.

The OVER Clause

Every window function uses OVER() to define the window:

function_name() OVER (
  PARTITION BY column    -- group rows
  ORDER BY column        -- sort within partition
  ROWS/RANGE frame       -- limit the window
)

PARTITION BY divides rows into groups (like GROUP BY, but without collapsing). ORDER BY sorts within each partition.

Ranking Functions

ROW_NUMBER — unique sequential number per partition:

SELECT *, ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) as rank
FROM employees;

RANK — same rank for ties, gaps after:

-- Scores: 100, 90, 90, 80 → Ranks: 1, 2, 2, 4
RANK() OVER (ORDER BY score DESC)

DENSE_RANK — same rank for ties, no gaps:

-- Scores: 100, 90, 90, 80 → Ranks: 1, 2, 2, 3
DENSE_RANK() OVER (ORDER BY score DESC)

LAG and LEAD

Access values from previous or next rows. Essential for period-over-period comparisons:

SELECT month, revenue,
  LAG(revenue, 1) OVER (ORDER BY month) as prev_month,
  revenue - LAG(revenue, 1) OVER (ORDER BY month) as change
FROM monthly_revenue;

LEAD looks forward instead of backward:

LEAD(revenue, 1) OVER (ORDER BY month) as next_month

Running Totals and Moving Averages

Running total:

SUM(amount) OVER (ORDER BY date ROWS UNBOUNDED PRECEDING)

Moving average (last 7 days):

AVG(amount) OVER (ORDER BY date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW)

Running total per category:

SUM(amount) OVER (PARTITION BY category ORDER BY date)

Real-World Example: Top N per Group

Get the top 3 products by revenue in each category:

WITH ranked AS (
  SELECT product, category, revenue,
    ROW_NUMBER() OVER (PARTITION BY category ORDER BY revenue DESC) as rn
  FROM products
)
SELECT * FROM ranked WHERE rn <= 3;

This pattern (CTE + ROW_NUMBER + filter) is one of the most common analytics patterns in SQL.