SQL Window Functions Explained with Examples + Practice Questions

What are Window Functions

Window functions perform calculations across a set of rows without grouping them into a single row.

Unlike GROUP BY, they:

• Keep all rows
• Add computed values alongside each row

When to Use Window Functions

• Ranking (top employees, leaderboards)
• Running totals
• Comparing rows (previous/next)
• Interview problems (very common for mid–senior roles)

Example Dataset

Table: employees

Basic Syntax

sql
FUNCTION() OVER (
    PARTITION BY column
    ORDER BY column
)

• OVER defines window
• PARTITION BY groups rows (like GROUP BY but keeps rows)
• ORDER BY defines order inside partition

ROW_NUMBER()

Assigns unique row number to each row.

sql
SELECT name, salary,
ROW_NUMBER() OVER (ORDER BY salary DESC) AS rank
FROM employees;

RANK()

Assigns rank (with gaps for ties).

sql
SELECT name, salary,
RANK() OVER (ORDER BY salary DESC) AS rank
FROM employees;

DENSE_RANK()

Assigns rank without gaps.

sql
SELECT name, salary,
DENSE_RANK() OVER (ORDER BY salary DESC) AS rank
FROM employees;

PARTITION BY Example

Rank employees within each department:

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

SUM() with Window (Running Total)

sql
SELECT name, salary,
SUM(salary) OVER (ORDER BY id) AS running_total
FROM employees;

AVG() with Window

sql
SELECT name, salary,
AVG(salary) OVER () AS avg_salary
FROM employees;

LAG() (Previous Row)

sql
SELECT name, salary,
LAG(salary) OVER (ORDER BY salary) AS previous_salary
FROM employees;

LEAD() (Next Row)

sql
SELECT name, salary,
LEAD(salary) OVER (ORDER BY salary) AS next_salary
FROM employees;

Key Concepts

• Window functions do not collapse rows
• PARTITION BY groups logically
• ORDER BY defines sequence
• Useful for ranking and comparisons

Window Functions vs GROUP BY

Common Questions (FAQ)

What is a window function

A function that performs calculations across rows without grouping them.

Difference between RANK and DENSE_RANK

RANK skips numbers on ties, DENSE_RANK does not.

When to use PARTITION BY

When you want calculations within groups.

Are window functions used in interviews

Yes, especially for ranking and analytics problems.

Internal Learning Links

• Learn Aggregations
• Learn GROUP BY
• Learn Subqueries

Practice CTA

Practice window function problems on Schoolabe to master advanced SQL queries.