SQL Window Functions — Interactive Tutorial

What Are Window Functions?

Window functions compute a value for each row based on a 'window' of related rows. Unlike aggregate functions with GROUP BY, window functions don't collapse rows — you get the computed value alongside every row. The OVER clause defines the window: PARTITION BY groups rows, ORDER BY sequences them within each partition.

ROW_NUMBER, RANK, and DENSE_RANK

These ranking functions assign numbers to rows within a partition. ROW_NUMBER gives unique sequential numbers (1,2,3,4). RANK leaves gaps for ties (1,2,2,4). DENSE_RANK has no gaps (1,2,2,3). Common use: top-N per group queries.

SELECT name, department, salary,
  ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS row_num,
  RANK() OVER (ORDER BY salary DESC) AS salary_rank
FROM employees;

Running Totals and Moving Averages

Use SUM() or AVG() with OVER to compute running totals or moving averages. The frame clause (ROWS BETWEEN) controls which rows are included: ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW for a running total, ROWS BETWEEN 2 PRECEDING AND CURRENT ROW for a 3-row moving average.

SELECT sale_date, amount,
  SUM(amount) OVER (ORDER BY sale_date) AS running_total,
  AVG(amount) OVER (
    ORDER BY sale_date
    ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
  ) AS moving_avg_3
FROM sales;

LAG and LEAD for Row Comparisons

LAG(column, offset) accesses a previous row's value; LEAD(column, offset) accesses a following row's value. These are essential for calculating period-over-period changes: LAG(revenue, 1) gives last month's revenue so you can compute month-over-month growth without self-joins.

SELECT month, revenue,
  LAG(revenue, 1) OVER (ORDER BY month) AS prev_month,
  revenue - LAG(revenue, 1) OVER (ORDER BY month) AS growth
FROM monthly_sales;