Introduction

Window functions are advanced SQL features used in data analysis to perform calculations across a set of rows without grouping them. Unlike GROUP BY, window functions allow you to keep individual rows while also computing rankings, running totals, and partitions. In this lesson, you will learn how to use window functions in SQL for powerful data analysis.

What are Window Functions in SQL

Window functions perform calculations across a window (set of rows) related to the current row. They are commonly used for ranking, cumulative sums, and partition-based analysis.

Basic syntax:
SELECT column_name,
function_name() OVER (PARTITION BY column_name ORDER BY column_name)
FROM table_name;

ROW_NUMBER Function

ROW_NUMBER assigns a unique number to each row within a result set.

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

This assigns a rank number to employees based on salary.

RANK Function

RANK assigns a rank to rows but allows duplicate ranks for equal values.

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

If two employees have the same salary, they get the same rank.

PARTITION BY Clause

PARTITION BY divides data into groups and applies window functions within each group.

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

This ranks employees within each department.

Difference Between ROW_NUMBER and RANK

• ROW_NUMBER gives unique numbers
• RANK gives same rank for equal values
• ROW_NUMBER does not skip numbers
• RANK may skip numbers after duplicates

Why Window Functions are Important in Data Analysis

Window functions help you:

• Perform ranking and comparisons
• Analyze data within groups
• Calculate running totals
• Build advanced reports and dashboards

Real-World Use Cases

• Ranking top-performing employees
• Finding top sales per region
• Analyzing trends within categories
• Creating leaderboards

Best Practices

• Use PARTITION BY for grouped analysis
• Combine with ORDER BY for meaningful results
• Avoid unnecessary window functions on large datasets
• Test queries for performance

Common Mistakes

• Confusing GROUP BY with window functions
• Missing ORDER BY in ranking functions
• Misusing PARTITION BY
• Ignoring performance impact

Summary

In this lesson, you learned how to use window functions like ROW_NUMBER, RANK, and PARTITION BY in SQL. These functions are essential for advanced data analysis and help you generate deeper insights without losing row-level details. In the next module, you will work on real-world SQL projects to apply your knowledge.

FAQs

1. What are window functions in SQL?
They are functions that perform calculations across a set of rows without grouping them.

2. What is ROW_NUMBER in SQL?
It assigns a unique number to each row.

3. What is the difference between RANK and ROW_NUMBER?
RANK allows duplicates, while ROW_NUMBER assigns unique values.

4. What is PARTITION BY used for?
It divides data into groups for analysis within each group.

Internal Link

Want to explore more courses?
Click here for more free courses