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

What are window functions?

Window functions compute values across a set of rows related to the current row, without collapsing them into a single output row (unlike GROUP BY).

-- Without window function: GROUP BY collapses rows
SELECT department, AVG(salary) FROM employees GROUP BY department;
-- 1 row per department

-- With window function: each row kept, aggregate added
SELECT name, department, salary,
    AVG(salary) OVER (PARTITION BY department) AS dept_avg
FROM employees;
-- Original rows preserved, dept_avg added to each

ROW_NUMBER, RANK, DENSE_RANK

These functions assign a number to each row within a partition.

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

-- Example output for ties (salary = 90000):
-- name    salary  row_num  rank  dense_rank
-- Alice   90000   1        1     1
-- Bob     90000   2        1     1
-- Carol   75000   3        3     2   <- RANK skips 2, DENSE_RANK doesn't

LEAD and LAG

LEAD looks ahead to the next row. LAG looks back at the previous row. Both are useful for comparing with adjacent rows.

SELECT
    date,
    revenue,
    LAG(revenue, 1, 0)  OVER (ORDER BY date) AS prev_revenue,
    LEAD(revenue, 1, 0) OVER (ORDER BY date) AS next_revenue,
    revenue - LAG(revenue) OVER (ORDER BY date) AS change
FROM monthly_revenue
ORDER BY date;

-- Find employees hired after their manager
SELECT e.name, e.hire_date, m.hire_date AS manager_hire_date
FROM employees e
JOIN employees m ON e.manager_id = m.id
WHERE e.hire_date < m.hire_date;

Running totals and moving averages

ROWS BETWEEN defines the window frame for cumulative calculations.

SELECT
    date,
    revenue,
    SUM(revenue) OVER (ORDER BY date) AS running_total,
    SUM(revenue) OVER (
        ORDER BY date
        ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
    ) AS rolling_3_day_sum,
    AVG(revenue) OVER (
        ORDER BY date
        ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
    ) AS rolling_7_day_avg
FROM daily_revenue
ORDER BY date;

Top N per group

A common use of window functions: find the top N rows per group.

-- Top 3 earners per department
SELECT name, department, salary
FROM (
    SELECT name, department, salary,
        ROW_NUMBER() OVER (
            PARTITION BY department
            ORDER BY salary DESC
        ) AS rn
    FROM employees
) ranked
WHERE rn <= 3;

Exam tip

The top-N-per-group pattern using ROW_NUMBER() in a subquery is the most common advanced SQL interview question. Also know RANK vs DENSE_RANK — the difference in how they handle ties is frequently asked.