Window Functions Explained: The Complete Guide

What Are Window Functions?

Window functions perform calculations across a set of rows that are related to the current row. Unlike GROUP BY, they don't collapse rows — you keep the detail while adding computed columns.

SELECT name, department, salary,
       AVG(salary) OVER (PARTITION BY department) as dept_avg
FROM employees;

This gives you each employee's salary alongside their department average, without losing individual rows.

The Syntax

function_name() OVER (
    [PARTITION BY column(s)]
    [ORDER BY column(s)]
    [ROWS/RANGE frame_clause]
)

• PARTITION BY — divides rows into groups (like GROUP BY but without collapsing)
• ORDER BY — defines the order within each partition
• Frame clause — defines which rows to include in the calculation

Ranking Functions

ROW_NUMBER()

Assigns a unique sequential number. No ties.

RANK()

Same values get the same rank. Gaps after ties.

DENSE_RANK()

Same values get the same rank. No gaps.

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;

-- Results with scores 95, 92, 92, 88:
-- row_num: 1, 2, 3, 4
-- rank:    1, 2, 2, 4
-- dense:   1, 2, 2, 3

Navigation Functions

LAG() and LEAD()

Access previous or next rows without a self-join:

-- Month-over-month revenue growth
SELECT month, revenue,
       LAG(revenue) OVER (ORDER BY month) as prev_month_rev,
       revenue - LAG(revenue) OVER (ORDER BY month) as growth
FROM monthly_revenue;

FIRST_VALUE() and LAST_VALUE()

-- Compare each employee's salary to the highest in their department
SELECT name, department, salary,
       FIRST_VALUE(salary) OVER (
           PARTITION BY department ORDER BY salary DESC
       ) as max_dept_salary
FROM employees;

Aggregate Window Functions

Any aggregate function can be used as a window function:

-- Running total of sales
SELECT order_date, amount,
       SUM(amount) OVER (ORDER BY order_date) as running_total
FROM orders;

-- Moving average (last 7 days)
SELECT order_date, amount,
       AVG(amount) OVER (
           ORDER BY order_date
           ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
       ) as moving_avg_7d
FROM daily_sales;

Common Interview Patterns

1. Top-N per group — RANK() + filter in subquery
2. Year-over-year comparison — LAG() with yearly partitions
3. Running totals — SUM() OVER (ORDER BY ...)
4. Deduplication — ROW_NUMBER() PARTITION BY + WHERE rn = 1

Practice Window Functions

Test your window function skills with our window functions problems — problems ranging from easy to hard with detailed solutions.