SQL Notes
Learn SQL Window Functions in detail, understand OVER clause, partitions, ranking functions, running totals, moving averages, and real-world analytics queries.
In the previous lesson, you learned about:
Recursive CTEswhich tackle hierarchical and recursive data problems.
Now we're hitting one of the most important Advanced SQL topics:
Window FunctionsWindow Functions are *everywhere* in:
Data Analytics
Business Intelligence
Reporting
Financial Systems
Data ScienceMost SQL interview questions and real-world reporting tasks rely on Window Functions. And for good reason—they're incredibly powerful.
Compare them to Aggregate Functions:
COUNT()
SUM()
AVG()
MIN()
MAX()Aggregate functions collapse your rows. Window Functions? They perform calculations across related rows *without* destroying your original data. That's the magic.
Simple Definition
A Window Function calculates values across a group of rows while keeping individual row details visible.
Why Are Window Functions Needed?
Say you have this:
| Employee | Salary |
|---|---|
| Rahul | 50000 |
| Priya | 60000 |
| Amit | 70000 |
You try:
SELECT AVG(Salary)
FROM Employees;Output:
| AvgSalary |
|---|
| ----------- |
| 60000 |
All your employee records vanish. Not good.
But what if you want:
Employee Name Employee Salary Average Salary
All on the same row?
Window Functions solve this.
Example
SELECT
EmployeeName,
Salary,
AVG(Salary)
OVER()
AS AverageSalary
FROM Employees;Output:
| Employee | Salary | AverageSalary |
|---|---|---|
| Rahul | 50000 | 60000 |
| Priya | 60000 | 60000 |
| Amit | 70000 | 60000 |
Rows stay. You get your calculation. Win-win.
How Window Functions Work
Window Functions work on:
Window
A window is just a set of rows that relate to your current row.
Think of it like:
Current Employee
↓
Related Employees
↓
Window Function
↓
Calculated ResultOVER Clause
This is the most important piece.
Syntax:
Function()
OVER(...)Without:
OVER()a Window Function can't work.
Basic Syntax
SELECT
ColumnName,
WindowFunction()
OVER()
FROM TableName;Example
SELECT
EmployeeName,
SUM(Salary)
OVER()
AS TotalSalary
FROM Employees;Output
| Employee | TotalSalary |
|---|---|
| Rahul | 180000 |
| Priya | 180000 |
| Amit | 180000 |
Types of Window Functions
There are a few categories:
Aggregate Window Functions Ranking Window Functions Value Window Functions Analytical Window Functions
Aggregate Window Functions
These are your standard aggregates wrapped in a window:
SUM()
AVG()
COUNT()
MIN()
MAX()used with:
OVER()SUM() Window Function
Example:
SELECT
EmployeeName,
Salary,
SUM(Salary)
OVER()
AS TotalSalary
FROM Employees;AVG() Window Function
SELECT
EmployeeName,
AVG(Salary)
OVER()
AS AvgSalary
FROM Employees;COUNT() Window Function
SELECT
EmployeeName,
COUNT(*)
OVER()
AS TotalEmployees
FROM Employees;PARTITION BY
One of the most important concepts you'll learn.
It lets you calculate values *within groups*.
Example Table
| Employee | Department | Salary |
|---|---|---|
| Rahul | HR | 50000 |
| Priya | HR | 60000 |
| Amit | IT | 70000 |
| Neha | IT | 80000 |
Average Salary by Department
SELECT
EmployeeName,
Department,
Salary,
AVG(Salary)
OVER
(
PARTITION BY Department
)
AS DepartmentAverage
FROM Employees;Output
| Employee | Department | Avg |
|---|---|---|
| Rahul | HR | 55000 |
| Priya | HR | 55000 |
| Amit | IT | 75000 |
| Neha | IT | 75000 |
Understanding PARTITION BY
Think of:
PARTITION BY
as:
GROUP BY
Without Losing RowsORDER BY in Window Functions
Sets the order of rows within your window.
Example
SELECT
EmployeeName,
Salary,
SUM(Salary)
OVER
(
ORDER BY Salary
)
AS RunningTotal
FROM Employees;Output
| Employee | Salary | RunningTotal |
|---|---|---|
| Rahul | 50000 | 50000 |
| Priya | 60000 | 110000 |
| Amit | 70000 | 180000 |
Running Total
This is huge for interviews and real reporting.
Example:
SELECT
EmployeeName,
SUM(Salary)
OVER
(
ORDER BY Salary
)
AS RunningTotal
FROM Employees;Moving Average
Example:
AVG(Salary)
OVER
(
ORDER BY Salary
)Used in:
Finance Stock Market Analysis Sales Analytics
Ranking Functions
Window Functions also include:
ROW_NUMBER()
RANK()
DENSE_RANK()
NTILE()These are so important we'll cover them separately in the next lesson.
Real-World Example: Banking
Your requirement:
Running Account BalanceWindow Function:
SUM(TransactionAmount)
OVER
(
ORDER BY TransactionDate
)Real-World Example: Sales Reporting
Your requirement:
Running Sales Total
Window Functions calculate it for you.
Real-World Example: Payroll
Your requirement:
Department Salary AveragePARTITION BY handles it.
Real-World Example: University System
Your requirement:
Student RankingWindow Functions provide it.
Window Function vs GROUP BY
GROUP BY:
Collapses RowsWindow Function:
Keeps RowsComparison
| Feature | GROUP BY | Window Function |
|---|---|---|
| Aggregation | Yes | Yes |
| Preserves Rows | No | Yes |
| Running Totals | No | Yes |
| Ranking | No | Yes |
| Partitioning | Limited | Powerful |
Advantages of Window Functions
Preserves Original Rows
The killer feature.
Supports Running Totals
Super useful for analytics.
Supports Ranking
Essential for reports.
Better Data Analysis
Gives advanced insights.
Powerful Reporting
Used everywhere in BI systems.
Disadvantages of Window Functions
More Complex
Harder if you're learning SQL.
Performance Cost
Large datasets need tuning.
Learning Curve
Takes time to understand partitions and windows.
Common Mistakes
Forgetting OVER()
The most common one.
Confusing PARTITION BY and GROUP BY
They don't work the same way.
Incorrect ORDER BY
Can give you wrong results.
Overusing Window Functions
Can slow things down.
Best Practices
Use Meaningful Aliases
Example:
AS RunningTotal
AS DepartmentAverageIndex Sorting Columns
Makes queries faster.
Use PARTITION BY Carefully
Don't create unnecessary partitions.
Test Large Datasets
Check execution plans.
Prefer Window Functions for Analytics
That's what they're built for.
Common Interview Questions
What is a Window Function?
A function that performs calculations across related rows while keeping individual row data.
What is the purpose of OVER()?
It defines the window for the function.
What is PARTITION BY?
It divides rows into groups for calculations.
Can Window Functions replace GROUP BY?
Not completely. They serve different purposes.
What is the main advantage of Window Functions?
They preserve original rows while calculating values.
Summary
Window Functions are one of SQL's most powerful features for analytics and reporting. They calculate values across groups of rows without losing individual row details, and they support running totals, departmental averages, rankings, and complex reporting.
In this lesson, you learned:
- What Window Functions are
- OVER() Clause basics
- PARTITION BY usage
- ORDER BY in windows
- Running Totals
- Moving Averages
- Aggregate Window Functions
- Real-world examples
- Best practices
Mastering Window Functions matters because they're used everywhere in modern analytics, business intelligence, reporting systems, and SQL interviews.
Next Step
Continue to the next lesson:
RANK(), DENSE_RANK(), and ROW_NUMBER() →
Exam Focus
Revise definitions, diagrams, examples, and short-answer points for Window Functions.
Interview Use
Prepare one clear explanation, one practical example, and one common mistake for this SQL Complete Guide topic.
Search Terms
sql-complete-guide, sql complete guide, sql, complete, guide, advanced, window, functions
Related SQL Complete Guide Topics