SQL Topics
Window Functions
title: Window Functions
In the previous lesson, you learned about:
Recursive CTEswhich help solve hierarchical and recursive data problems.
Now we move to one of the most important topics in Advanced SQL:
Window FunctionsWindow Functions are heavily used in:
Data Analytics
Business Intelligence
Reporting
Financial Systems
Data ScienceMany SQL interview questions and real-world reporting tasks rely on Window Functions.
Unlike Aggregate Functions:
COUNT()
SUM()
AVG()
MIN()
MAX()Window Functions perform calculations across related rows while still returning individual row data.
This makes them extremely powerful.
What is a Window Function?
A Window Function performs calculations across a set of rows related to the current row without collapsing the result into a single row.
Unlike GROUP BY:
GROUP BY
Combines RowsWindow Functions:
Keep Original Rows
+
Perform CalculationsSimple Definition
A Window Function calculates values across a group of rows while preserving individual row details.
Why Are Window Functions Needed?
Suppose we have:
| Employee | Salary |
|---|---|
| Rahul | 50000 |
| Priya | 60000 |
| Amit | 70000 |
If we calculate:
SELECT AVG(Salary)
FROM Employees;Output:
| AvgSalary |
|---|
| ------------ |
| 60000 |
Individual employee records disappear.
But what if we want:
Employee Name
Employee Salary
Average Salarytogether?
Window Functions solve this problem.
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 remain visible.
How Window Functions Work
Window Functions operate on:
WindowA window is a set of rows related to the current row.
Example:
Current Employee
↓
Related Employees
↓
Window Function
↓
Calculated ResultOVER Clause
The most important component.
Syntax:
Function()
OVER(...)Without:
OVER()a Window Function cannot 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
Major categories:
Aggregate Window Functions
Ranking Window Functions
Value Window Functions
Analytical Window FunctionsAggregate Window Functions
Examples:
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.
Allows calculations 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 BYas:
GROUP BY
Without Losing RowsORDER BY in Window Functions
Determines row order within a 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
A common interview topic.
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 AnalyticsRanking Functions
Window Functions include:
ROW_NUMBER()
RANK()
DENSE_RANK()
NTILE()These are so important that they will be covered in the next lesson separately.
Real-World Example: Banking
Requirement:
Running Account BalanceWindow Function:
SUM(TransactionAmount)
OVER
(
ORDER BY TransactionDate
)Real-World Example: Sales Reporting
Requirement:
Running Sales TotalWindow Functions calculate cumulative sales.
Real-World Example: Payroll
Requirement:
Department Salary AveragePARTITION BY handles this.
Real-World Example: University System
Requirement:
Student RankingWindow Functions provide rankings.
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
Major advantage.
Supports Running Totals
Very useful for analytics.
Supports Ranking
Essential for reports.
Better Data Analysis
Provides advanced insights.
Powerful Reporting
Widely used in BI systems.
Disadvantages of Window Functions
More Complex
Harder for beginners.
Performance Cost
Large datasets require optimization.
Learning Curve
Requires understanding of partitions and windows.
Common Mistakes
Forgetting OVER()
Most common error.
Confusing PARTITION BY and GROUP BY
They behave differently.
Incorrect ORDER BY
Can produce wrong results.
Overusing Window Functions
May impact performance.
Best Practices
Use Meaningful Aliases
Example:
AS RunningTotal
AS DepartmentAverageIndex Sorting Columns
Improve performance.
Use PARTITION BY Carefully
Avoid unnecessary partitions.
Test Large Datasets
Monitor execution plans.
Prefer Window Functions for Analytics
They are designed for reporting.
Common Interview Questions
What is a Window Function?
A function that performs calculations across related rows while preserving individual row data.
What is the purpose of OVER()?
Defines the window used by the function.
What is PARTITION BY?
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 performing calculations.
Summary
Window Functions are one of the most powerful SQL features for analytics and reporting. They allow calculations across groups of rows without losing individual row details and support advanced operations like running totals, departmental averages, rankings, and analytical reporting.
In this lesson, you learned:
- What Window Functions are
- OVER() Clause
- PARTITION BY
- ORDER BY
- Running Totals
- Moving Averages
- Aggregate Window Functions
- Real-world examples
- Best practices
Mastering Window Functions is essential because they are heavily used 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 topic.
Search Terms
sql, sql complete guide, sql tutorial, sql notes, complete, guide, advanced, window
Related SQL Topics