Eric Campbell Feb 17, 2026 0 min read SQL

SQL Window Functions: A Complete Guide

Window functions let you perform calculations across rows related to the current row without collapsing the result set.

Key Insights

Window functions compute values across a set of rows without reducing the number of rows returned
ROW_NUMBER, RANK, and DENSE_RANK solve most ranking problems
LAG/LEAD enable row-to-row comparisons without self-joins

Basic Syntax

SELECT
    employee_name,
    department,
    salary,
    AVG(salary) OVER (PARTITION BY department) as dept_avg,
    salary - AVG(salary) OVER (PARTITION BY department) as diff_from_avg
FROM employees;

Ranking Functions

SELECT
    product_name,
    category,
    revenue,
    ROW_NUMBER() OVER (PARTITION BY category ORDER BY revenue DESC) as rank
FROM products;

LAG and LEAD

SELECT
    date,
    revenue,
    LAG(revenue, 1) OVER (ORDER BY date) as prev_day,
    revenue - LAG(revenue, 1) OVER (ORDER BY date) as daily_change
FROM daily_sales;