SQL Window Functions: ROW_NUMBER, RANK, LAG, and LEAD

SQL Window Functions: ROW_NUMBER, RANK, LAG, and LEAD

Window functions perform calculations across a set of rows related to the current row without collapsing them into a single output row like GROUP BY does. They use the OVER clause to define the window.

Basic Syntax

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

Ranking Functions

SELECT name, score,
  ROW_NUMBER() OVER (ORDER BY score DESC) AS row_num,
  RANK()       OVER (ORDER BY score DESC) AS rank,
  DENSE_RANK() OVER (ORDER BY score DESC) AS dense_rank
FROM students;

• ROW_NUMBER: Unique sequential number, no ties (1, 2, 3, 4)
• RANK: Same rank for ties, gaps after ties (1, 2, 2, 4)
• DENSE_RANK: Same rank for ties, no gaps (1, 2, 2, 3)

LAG and LEAD

Access previous or next row values without self-joins:

SELECT month, revenue,
  LAG(revenue, 1) OVER (ORDER BY month) AS prev_month,
  LEAD(revenue, 1) OVER (ORDER BY month) AS next_month,
  revenue - LAG(revenue, 1) OVER (ORDER BY month) AS month_over_month
FROM monthly_revenue;

Running Totals and Moving Averages

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

Named Windows

SELECT name, department, salary,
  SUM(salary) OVER w AS dept_total,
  AVG(salary) OVER w AS dept_avg
FROM employees
WINDOW w AS (PARTITION BY department);

Performance Notes

• Window functions require sorting by the PARTITION BY and ORDER BY columns
• Adding an index matching these columns can eliminate a sort step
• Window functions are generally more efficient than correlated subqueries for the same result

Window functions are essential for analytics, reporting, and any scenario where row-level detail must coexist with aggregate calculations.