SQL Window Functions: A Practical Guide

Window functions perform calculations across a set of rows related to the current row without collapsing them. They are essential for ranking, running totals, and comparing rows.

Syntax

function_name() OVER (
  [PARTITION BY column]
  [ORDER BY column [ASC|DESC]]
  [ROWS BETWEEN ...]
)

ROW_NUMBER, RANK, DENSE_RANK

SELECT name, department, salary,
  ROW_NUMBER() OVER (ORDER BY salary DESC) as row_num,
  RANK() OVER (ORDER BY salary DESC) as rank,
  DENSE_RANK() OVER (ORDER BY salary DESC) as dense_rank
FROM employees;

• ROW_NUMBER — unique sequential number (1, 2, 3, 4)
• RANK — same rank for ties, skips numbers (1, 2, 2, 4)
• DENSE_RANK — same rank for ties, no skip (1, 2, 2, 3)

LAG and LEAD

Access values from previous or next rows.

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

Running Totals with SUM OVER

SELECT date, amount,
  SUM(amount) OVER (ORDER BY date) as running_total
FROM transactions;

Partitioned Rankings

Rank within groups using PARTITION BY.

SELECT name, department, salary,
  RANK() OVER (PARTITION BY department ORDER BY salary DESC) as dept_rank
FROM employees;

This ranks employees within each department separately.

NTILE — Dividing Rows into Buckets

SELECT name, salary,
  NTILE(4) OVER (ORDER BY salary DESC) as quartile
FROM employees;

Divides employees into 4 groups by salary (top 25%, 25-50%, etc.).

Window Functions vs GROUP BY

• GROUP BY collapses rows — one output row per group
• Window functions keep all rows — calculation added as a new column

Practice

Format your window function queries with the SQL Formatter for better readability. Complex OVER clauses are much easier to review when properly indented.