Complete Guide to SQL Window Functions for Data Professionals

If you are learning SQL for Data Engineering, Data Analysis, Data Science, or BI roles, then Window Functions are one of the most important concepts you must master.

Most beginners learn GROUP BY first. But when they start solving real-world business problems, they quickly realize:

I want totals without losing row-level data
I want to rank employees
I want previous row values
I want running totals
I want top products in each category

That is exactly where Window Functions become powerful.

In this blog, we will learn Window Functions from scratch with:

Beginner-friendly explanations
Real examples
Step-by-step breakdowns
Business use cases
Interview-level understanding

By the end of this blog, you will be able to confidently use Window Functions in real projects and interviews.

What Are Window Functions?

A Window Function performs calculations across a set of rows related to the current row without collapsing the rows.

This is the biggest difference between GROUP BY and Window Functions.

GROUP BY	Window Function
Reduces rows	Keeps original rows
Gives one output per group	Gives output for every row
Used for aggregation	Used for analytics

Understanding with a Simple Example

Imagine we have employee salary data.

emp_id	name	department	salary
1	Rahul	IT	50000
2	Priya	IT	70000
3	Amit	HR	40000
4	Sneha	HR	45000

Problem Statement

We want:

Employee details
Along with average salary of their department

If we use GROUP BY, we lose employee-level rows.

Using GROUP BY

SELECT department,
       AVG(salary) AS avg_salary
FROM employees
GROUP BY department;

Output

department	avg_salary
IT	60000
HR	42500

But where did employee rows go?

This is where Window Functions help.

First Window Function Example

SELECT emp_id,
       name,
       department,
       salary,
       AVG(salary) OVER(
           PARTITION BY department
       ) AS dept_avg_salary
FROM employees;

Output

emp_id	name	department	salary	dept_avg_salary
1	Rahul	IT	50000	60000
2	Priya	IT	70000	60000
3	Amit	HR	40000	42500
4	Sneha	HR	45000	42500

Now we still have:

Original rows
Additional analytics

That is the beauty of Window Functions.

Structure of a Window Function

General syntax:

FUNCTION_NAME(column)
OVER(
    PARTITION BY column
    ORDER BY column
)

Let us understand each part.

OVER() Clause

OVER() tells SQL:

Apply this function as a Window Function.

Without OVER(), it behaves like a normal aggregate function.

Example

AVG(salary)

Normal aggregate.

AVG(salary) OVER()

Window Function.

PARTITION BY

PARTITION BY divides data into groups.

Think of it like:

GROUP BY without collapsing rows.

Example

AVG(salary) OVER(
    PARTITION BY department
)

This means:

Create separate windows for each department
Calculate average inside each department

ORDER BY in Window Functions

Inside Window Functions, ORDER BY controls row sequence.

Example

SUM(salary) OVER(
    ORDER BY emp_id
)

This creates a running total.

Running Total Example

Query

SELECT emp_id,
       name,
       salary,
       SUM(salary) OVER(
           ORDER BY emp_id
       ) AS running_salary
FROM employees;

Output

emp_id	salary	running_salary
1	50000	50000
2	70000	120000
3	40000	160000
4	45000	205000

Real Business Use Cases

Window Functions are heavily used in:

Sales analytics
Financial reporting
Customer behavior analysis
Data warehousing
KPI dashboards
Time-series analysis
Ranking systems

Most Important Window Functions

Every data professional should know these:

ROW_NUMBER()
RANK()
DENSE_RANK()
LEAD()
LAG()
SUM()
AVG()
FIRST_VALUE()
LAST_VALUE()

ROW_NUMBER()

Assigns unique row numbers.

Example

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

Output

name	department	salary	row_num
Priya	IT	70000	1
Rahul	IT	50000	2
Sneha	HR	45000	1
Amit	HR	40000	2

Use Cases

Top N records
Latest records
Duplicate removal

Finding Highest Salary Employee in Each Department

WITH ranked_data AS (

    SELECT *,
           ROW_NUMBER() OVER(
               PARTITION BY department
               ORDER BY salary DESC
           ) AS rn
    FROM employees

)

SELECT *
FROM ranked_data
WHERE rn = 1;

This is one of the most common SQL interview questions.

RANK()

Similar to ROW_NUMBER(), but ties get the same rank.

Example Data

name	salary
A	100
B	100
C	90

Using RANK()

name	rank
A	1
B	1
C	3

Notice:

Rank 2 is skipped

DENSE_RANK()

Similar to RANK(), but without gaps.

name	dense_rank
A	1
B	1
C	2

Difference Between ROW_NUMBER, RANK, and DENSE_RANK

Function	Duplicate Rank Allowed	Skips Rank
ROW_NUMBER	No	No
RANK	Yes	Yes
DENSE_RANK	Yes	No

LAG()

Gets the previous row value.

Example

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

Output

emp_id	salary	previous_salary
1	50000	NULL
2	70000	50000
3	40000	70000
4	45000	40000

Real Use Cases

Day-over-day sales comparison
Stock price movement
Revenue growth analysis
Customer purchase tracking

LEAD()

Opposite of LAG().

Gets next row value.

Example

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

Difference Between LAG and LEAD

Function	Retrieves
LAG	Previous row
LEAD	Next row

Window Frame Concept

A window frame defines:

Which rows should participate in calculation?

Example

SUM(salary) OVER(
    ORDER BY emp_id
    ROWS BETWEEN UNBOUNDED PRECEDING
    AND CURRENT ROW
)

Meaning:

Start from first row
Continue till current row

This creates cumulative totals.

Important Window Frame Keywords

Keyword	Meaning
UNBOUNDED PRECEDING	Start from first row
CURRENT ROW	Current row
UNBOUNDED FOLLOWING	Till last row

Common Interview Questions

Difference Between GROUP BY and Window Functions

GROUP BY reduces rows
Window Functions retain rows

Difference Between RANK and DENSE_RANK

RANK() skips numbers after ties
DENSE_RANK() does not skip numbers

When Should We Use ROW_NUMBER()

Use when unique numbering is needed.

Examples:

Remove duplicates
Find latest records

Real-World Data Engineering Use Cases

Window Functions are heavily used in:

Incremental data pipelines
CDC processing
Deduplication logic
SCD Type 2 implementation
Data quality checks
KPI calculations

Example: Removing Duplicate Records

WITH duplicate_check AS (

    SELECT *,
           ROW_NUMBER() OVER(
               PARTITION BY customer_id
               ORDER BY updated_date DESC
           ) AS rn
    FROM customers

)

SELECT *
FROM duplicate_check
WHERE rn = 1;

This keeps only the latest record per customer.

This pattern is used heavily in real-world Data Engineering projects.

Performance Tips

Window Functions are powerful but expensive on huge datasets.

Best practices:

Use proper indexing
Partition wisely
Avoid unnecessary sorting
Process data in stages
Use distributed engines carefully

Common Mistakes Beginners Make

Forgetting OVER()

Wrong:

AVG(salary)

Correct:

AVG(salary) OVER()

Confusing GROUP BY with PARTITION BY

Remember:

GROUP BY collapses rows
PARTITION BY keeps rows

Missing ORDER BY in Ranking Functions

Ranking functions require ordering.

20 Best SQL Window Function Practice Questions

If you can solve these 20 questions confidently, you will be comfortable with most real-world and interview-level Window Function problems:

Find the highest-paid employee in each department.
Find the second-highest salary in each department.
Find the top 3 employees from each department based on salary.
Show each employee with their department average salary.
Find employees earning more than their department average salary.
Find duplicate customer records and keep only the latest record.
Find customers whose city changed over time.
Calculate running total of daily sales.
Calculate running total of sales separately for each region.
Compare today’s sales with previous day’s sales.
Calculate month-over-month revenue growth.
Find the first order of every customer.
Find the latest order of every customer.
Find the gap in days between two consecutive orders of a customer.
Find customers who came back after more than 30 days.
Find top 3 products by sales in each category.
Find each product’s contribution percentage to total sales.
Calculate 3-day moving average of sales.
Find users who logged in on consecutive days.
Find the latest status of every order or ticket.

Quick Revision Cheat Sheet

Function	Purpose
ROW_NUMBER()	Unique numbering
RANK()	Ranking with gaps
DENSE_RANK()	Ranking without gaps
LAG()	Previous row
LEAD()	Next row
SUM() OVER()	Running total
AVG() OVER()	Moving averages

Conclusion

Window Functions are not just an interview topic.

They are one of the most practical and frequently used SQL concepts in real-world data projects.

Mastering them will help you:

Write advanced SQL queries
Solve business problems efficiently
Perform analytics professionally
Crack data interviews confidently

If you are serious about becoming a strong data professional, Window Functions are absolutely worth mastering deeply.

SQL Window Functions for Data Professionals

SQL Fundamentals

SQL Window Functions for Data Professionals

Complete Guide to SQL Window Functions for Data Professionals

What Are Window Functions?

Understanding with a Simple Example

Problem Statement

Using GROUP BY

Output

First Window Function Example

Output

Structure of a Window Function

OVER() Clause

Example

PARTITION BY

Example

ORDER BY in Window Functions

Example

Running Total Example

Query

Output

Real Business Use Cases

Most Important Window Functions

ROW_NUMBER()

Example

Output

Use Cases

Finding Highest Salary Employee in Each Department

RANK()

Example Data

Using RANK()

DENSE_RANK()

Difference Between ROW_NUMBER, RANK, and DENSE_RANK

LAG()

Example

Output

Real Use Cases

LEAD()

Example

Difference Between LAG and LEAD

Window Frame Concept

Example

Important Window Frame Keywords

Common Interview Questions

Difference Between GROUP BY and Window Functions

Difference Between RANK and DENSE_RANK

When Should We Use ROW_NUMBER()

Real-World Data Engineering Use Cases

Example: Removing Duplicate Records

Performance Tips

Common Mistakes Beginners Make

Forgetting OVER()

Confusing GROUP BY with PARTITION BY

Missing ORDER BY in Ranking Functions

20 Best SQL Window Function Practice Questions

Quick Revision Cheat Sheet

Conclusion

Soumya Ranjan Bisoyi