SQL Window Function Tutorial: ROW_NUMBER, RANK, LAG Practical Applications [Complete Guide]

Window Functions are one of the most powerful tools in advanced SQL queries, enabling ranking, cumulative calculations, and previous/next value comparisons without changing the number of data rows. This article will systematically introduce the core concepts and practical applications of window functions, helping you solve complex business analysis requirements.

Window Function Concepts

What are Window Functions?

Window functions perform calculations on a set of related data rows (called a "window" or "frame") and return one result value for each row. Unlike aggregate functions, window functions do not merge multiple rows into one row.

-- Aggregate function: 5 rows become 1 row
SELECT Department, SUM(Salary) AS TotalSalary
FROM Employees
GROUP BY Department;

-- Window function: 5 rows remain 5 rows, but with calculation results added
SELECT
    Name,
    Department,
    Salary,
    SUM(Salary) OVER (PARTITION BY Department) AS DeptTotalSalary
FROM Employees;

Differences from Aggregate Functions

Characteristic	Aggregate Functions	Window Functions
Result rows	Merged into one row	Preserves original row count
GROUP BY	Must use	Not required
Original data	Cannot access	Can access simultaneously
Use cases	Summary statistics	Ranking, cumulative, comparison

OVER() Syntax Structure

The core of window functions is the OVER() clause:

function_name() OVER (
    [PARTITION BY partition_column]
    [ORDER BY sort_column]
    [ROWS/RANGE frame_specification]
)

Basic Example:

SELECT
    Name,
    Department,
    Salary,
    -- Company-wide salary ranking
    ROW_NUMBER() OVER (ORDER BY Salary DESC) AS CompanyRank,
    -- Department salary ranking
    ROW_NUMBER() OVER (PARTITION BY Department ORDER BY Salary DESC) AS DeptRank
FROM Employees;

For more SQL advanced features, refer to SQL Advanced Techniques Practical Tips.

PARTITION BY Partitioning

PARTITION BY divides data into multiple groups, and window functions calculate independently within each group.

Basic Usage

-- Calculate salary ranking within each department
SELECT
    EmployeeId,
    Name,
    Department,
    Salary,
    ROW_NUMBER() OVER (
        PARTITION BY Department  -- Partition by department
        ORDER BY Salary DESC     -- Order by salary
    ) AS DeptSalaryRank
FROM Employees;

Execution Result:

EmployeeId	Name	Department	Salary	DeptSalaryRank
101	Alice	Sales	80000	1
102	Bob	Sales	70000	2
103	Carol	Sales	60000	3
201	David	Engineering	90000	1
202	Eve	Engineering	85000	2

Multi-Column Partitioning

-- Partition by department and year
SELECT
    EmployeeId,
    Name,
    Department,
    YEAR(HireDate) AS HireYear,
    Salary,
    ROW_NUMBER() OVER (
        PARTITION BY Department, YEAR(HireDate)
        ORDER BY Salary DESC
    ) AS RankInDeptYear
FROM Employees;

Without PARTITION BY

If PARTITION BY is not specified, the entire result set is treated as one partition:

-- Company-wide salary ranking (no partition)
SELECT
    Name,
    Salary,
    ROW_NUMBER() OVER (ORDER BY Salary DESC) AS CompanyRank
FROM Employees;

Numbering Functions

Numbering functions assign a numeric identifier to each row, commonly used for ranking and pagination.

ROW_NUMBER()

Assigns a unique consecutive number to each row. Even with same values, different numbers are assigned.

SELECT
    Name,
    Department,
    Salary,
    ROW_NUMBER() OVER (ORDER BY Salary DESC) AS RowNum
FROM Employees;

Result:

Name	Salary	RowNum
David	90000	1
Eve	85000	2
Alice	80000	3
Frank	80000	4

Common Application: Pagination Query

-- Get page 2 data (10 rows per page)
WITH NumberedEmployees AS (
    SELECT
        *,
        ROW_NUMBER() OVER (ORDER BY EmployeeId) AS RowNum
    FROM Employees
)
SELECT * FROM NumberedEmployees
WHERE RowNum BETWEEN 11 AND 20;

RANK()

Same values get the same rank, but subsequent ranks are skipped.

SELECT
    Name,
    Salary,
    RANK() OVER (ORDER BY Salary DESC) AS SalaryRank
FROM Employees;

Result:

Name	Salary	SalaryRank
David	90000	1
Eve	85000	2
Alice	80000	3
Frank	80000	3
Bob	70000	5

DENSE_RANK()

Same values get the same rank, but subsequent ranks are NOT skipped.

SELECT
    Name,
    Salary,
    DENSE_RANK() OVER (ORDER BY Salary DESC) AS DenseRank
FROM Employees;

Result:

Name	Salary	DenseRank
David	90000	1
Eve	85000	2
Alice	80000	3
Frank	80000	3
Bob	70000	4

Comparing Three Numbering Functions

SELECT
    Name,
    Salary,
    ROW_NUMBER() OVER (ORDER BY Salary DESC) AS RowNum,
    RANK() OVER (ORDER BY Salary DESC) AS Rank,
    DENSE_RANK() OVER (ORDER BY Salary DESC) AS DenseRank
FROM Employees
ORDER BY Salary DESC;

Name	Salary	RowNum	Rank	DenseRank
David	90000	1	1	1
Eve	85000	2	2	2
Alice	80000	3	3	3
Frank	80000	4	3	3
Bob	70000	5	5	4

NTILE()

Divides data evenly into N groups:

-- Divide employees into 4 groups by salary (quartiles)
SELECT
    Name,
    Salary,
    NTILE(4) OVER (ORDER BY Salary DESC) AS Quartile
FROM Employees;

Application Scenarios:

• Divide customers into VIP/Regular/Low-value segments
• Calculate percentiles
• Evenly distribute workload

Previous/Next Value Functions

Previous/next value functions are used to access data values from rows before or after the current row, ideal for calculating changes and trend analysis.

LAG() - Get Previous Row Value

LAG(column, offset, default_value) OVER (PARTITION BY ... ORDER BY ...)

-- Calculate monthly sales compared to previous month
SELECT
    Month,
    Sales,
    LAG(Sales, 1, 0) OVER (ORDER BY Month) AS PrevMonthSales,
    Sales - LAG(Sales, 1, 0) OVER (ORDER BY Month) AS MoM_Change
FROM MonthlySales;

Result:

Month	Sales	PrevMonthSales	MoM_Change
2024-01	100000	0	100000
2024-02	120000	100000	20000
2024-03	115000	120000	-5000

LEAD() - Get Next Row Value

-- View next order information
SELECT
    OrderId,
    OrderDate,
    LEAD(OrderDate, 1) OVER (
        PARTITION BY CustomerId
        ORDER BY OrderDate
    ) AS NextOrderDate,
    DATEDIFF(DAY, OrderDate,
        LEAD(OrderDate, 1) OVER (
            PARTITION BY CustomerId
            ORDER BY OrderDate
        )
    ) AS DaysToNextOrder
FROM Orders;

FIRST_VALUE() / LAST_VALUE()

Get the first or last value within the window:

SELECT
    EmployeeId,
    Name,
    Department,
    Salary,
    FIRST_VALUE(Name) OVER (
        PARTITION BY Department
        ORDER BY Salary DESC
    ) AS HighestPaidInDept,
    FIRST_VALUE(Salary) OVER (
        PARTITION BY Department
        ORDER BY Salary DESC
    ) AS MaxSalaryInDept
FROM Employees;

LAST_VALUE Note:

-- Need to specify frame range, otherwise results may be unexpected
SELECT
    Name,
    Salary,
    LAST_VALUE(Name) OVER (
        ORDER BY Salary DESC
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ) AS LowestPaidEmployee
FROM Employees;

Aggregate Window Functions

Traditional aggregate functions with OVER() become window functions, performing summary calculations while preserving original data rows.

Running Total

-- Calculate cumulative sales
SELECT
    OrderDate,
    Amount,
    SUM(Amount) OVER (ORDER BY OrderDate) AS RunningTotal
FROM Orders;

Result:

OrderDate	Amount	RunningTotal
2024-01-01	1000	1000
2024-01-02	1500	2500
2024-01-03	800	3300
2024-01-04	2000	5300

Cumulative by Department:

SELECT
    Department,
    OrderDate,
    Amount,
    SUM(Amount) OVER (
        PARTITION BY Department
        ORDER BY OrderDate
    ) AS DeptRunningTotal
FROM Orders;

Moving Average

-- Calculate 7-day moving average
SELECT
    Date,
    Sales,
    AVG(Sales) OVER (
        ORDER BY Date
        ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
    ) AS MovingAvg7Days
FROM DailySales;

Frame Range Explanation:

• ROWS BETWEEN 6 PRECEDING AND CURRENT ROW: Current row and 6 preceding rows
• ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW: From beginning to current row
• ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING: From current row to end

Other Aggregate Window Functions

SELECT
    Department,
    Name,
    Salary,
    -- Department average salary
    AVG(Salary) OVER (PARTITION BY Department) AS DeptAvgSalary,
    -- Department maximum salary
    MAX(Salary) OVER (PARTITION BY Department) AS DeptMaxSalary,
    -- Department minimum salary
    MIN(Salary) OVER (PARTITION BY Department) AS DeptMinSalary,
    -- Department headcount
    COUNT(*) OVER (PARTITION BY Department) AS DeptHeadCount,
    -- Salary difference from department average
    Salary - AVG(Salary) OVER (PARTITION BY Department) AS DiffFromAvg
FROM Employees;

Practical Case: Sales Ranking Analysis

Here's a complete sales analysis case combining multiple window functions.

Scenario Description

Analyze sales representatives' performance, requiring:

1. Company-wide sales ranking
2. Regional sales ranking
3. Comparison with previous month's performance
4. Cumulative achievement rate

Complete Query

WITH SalesData AS (
    SELECT
        s.SalesRepId,
        e.Name AS SalesRepName,
        e.Region,
        s.SalesMonth,
        s.SalesAmount,
        s.TargetAmount
    FROM MonthlySales s
    JOIN Employees e ON s.SalesRepId = e.EmployeeId
    WHERE YEAR(s.SalesMonth) = 2024
)
SELECT
    SalesRepName,
    Region,
    SalesMonth,
    SalesAmount,
    TargetAmount,

    -- 1. Company-wide monthly ranking
    RANK() OVER (
        PARTITION BY SalesMonth
        ORDER BY SalesAmount DESC
    ) AS CompanyRank,

    -- 2. Regional monthly ranking
    RANK() OVER (
        PARTITION BY Region, SalesMonth
        ORDER BY SalesAmount DESC
    ) AS RegionRank,

    -- 3. Previous month's sales
    LAG(SalesAmount, 1, 0) OVER (
        PARTITION BY SalesRepId
        ORDER BY SalesMonth
    ) AS PrevMonthSales,

    -- 4. Month-over-month growth rate
    CASE
        WHEN LAG(SalesAmount, 1, 0) OVER (
            PARTITION BY SalesRepId ORDER BY SalesMonth
        ) = 0 THEN NULL
        ELSE ROUND(
            (SalesAmount - LAG(SalesAmount, 1, 0) OVER (
                PARTITION BY SalesRepId ORDER BY SalesMonth
            )) * 100.0 / LAG(SalesAmount, 1, 0) OVER (
                PARTITION BY SalesRepId ORDER BY SalesMonth
            ), 2)
    END AS MoM_GrowthRate,

    -- 5. Year-to-date sales
    SUM(SalesAmount) OVER (
        PARTITION BY SalesRepId
        ORDER BY SalesMonth
        ROWS UNBOUNDED PRECEDING
    ) AS YTD_Sales,

    -- 6. Year-to-date target
    SUM(TargetAmount) OVER (
        PARTITION BY SalesRepId
        ORDER BY SalesMonth
        ROWS UNBOUNDED PRECEDING
    ) AS YTD_Target,

    -- 7. Cumulative achievement rate
    ROUND(
        SUM(SalesAmount) OVER (
            PARTITION BY SalesRepId
            ORDER BY SalesMonth
            ROWS UNBOUNDED PRECEDING
        ) * 100.0 /
        NULLIF(SUM(TargetAmount) OVER (
            PARTITION BY SalesRepId
            ORDER BY SalesMonth
            ROWS UNBOUNDED PRECEDING
        ), 0), 2
    ) AS YTD_AchievementRate

FROM SalesData
ORDER BY SalesMonth, CompanyRank;

Advanced Analysis: Find Sales Reps with Consecutive Growth

WITH GrowthAnalysis AS (
    SELECT
        SalesRepId,
        SalesMonth,
        SalesAmount,
        LAG(SalesAmount) OVER (
            PARTITION BY SalesRepId ORDER BY SalesMonth
        ) AS PrevAmount,
        CASE
            WHEN SalesAmount > LAG(SalesAmount) OVER (
                PARTITION BY SalesRepId ORDER BY SalesMonth
            ) THEN 1
            ELSE 0
        END AS IsGrowth
    FROM MonthlySales
)
SELECT
    SalesRepId,
    COUNT(*) AS ConsecutiveGrowthMonths
FROM GrowthAnalysis
WHERE IsGrowth = 1
GROUP BY SalesRepId
HAVING COUNT(*) >= 3
ORDER BY ConsecutiveGrowthMonths DESC;

Performance Considerations

Window functions are powerful but need attention to performance impact.

Performance Optimization Tips

1. Create Appropriate Indexes

   -- Create index for PARTITION BY and ORDER BY columns
   CREATE INDEX IX_Sales_Rep_Month
   ON MonthlySales (SalesRepId, SalesMonth)
   INCLUDE (SalesAmount);
   

2. Reduce Window Function Count

   -- Worse: Repeat calculation for same window
   SELECT
       LAG(Amount) OVER (ORDER BY Date) AS Prev,
       LEAD(Amount) OVER (ORDER BY Date) AS Next
   FROM Sales;
   
   -- Better: Share same window definition
   SELECT
       LAG(Amount) OVER w AS Prev,
       LEAD(Amount) OVER w AS Next
   FROM Sales
   WINDOW w AS (ORDER BY Date);  -- SQL Server 2022+ support
   

3. Limit Data Volume

   -- Filter first, then calculate
   WITH FilteredData AS (
       SELECT * FROM Sales WHERE Year = 2024
   )
   SELECT *, ROW_NUMBER() OVER (ORDER BY Amount DESC)
   FROM FilteredData;
   

For more performance optimization tips, refer to SQL Performance Tuning Complete Guide.

Conclusion

Window functions are powerful tools for handling complex analysis needs in SQL. Master the core features introduced in this article:

1. ✅ Understand OVER() and PARTITION BY syntax
2. ✅ Use ROW_NUMBER, RANK, DENSE_RANK ranking functions effectively
3. ✅ Apply LAG, LEAD for previous/next value comparisons
4. ✅ Use aggregate window functions for cumulative and moving averages
5. ✅ Pay attention to performance optimization and index design

Window functions can greatly simplify complex queries that would otherwise require subqueries or self-joins, making them essential advanced skills for data analysts and backend engineers.

For more stored procedure development, refer to SQL Stored Procedure Tutorial.

---

CloudInsight End-of-Article CTA

Want to Learn Advanced Data Analysis SQL Techniques?

CloudInsight provides professional advanced SQL training courses, including:

• Complete window functions practical exercises
• Complex report query design
• Performance optimization and execution plan analysis
• Real business case analysis

Learn About Data Analysis Advanced SQL Courses →

---

FAQ

What's the Difference Between ROW_NUMBER and RANK?

The key difference is how they handle same values:

ROW_NUMBER():

• Always returns unique consecutive numbers
• Same values get different numbers (order determined by database)
• Result: 1, 2, 3, 4, 5...

RANK():

• Same values get the same rank
• Subsequent ranks are skipped after same rank
• Result: 1, 2, 3, 3, 5... (skips 4)

Selection Guide:

Requirement	Recommended Function
Pagination query (needs unique numbers)	ROW_NUMBER
Leaderboard (allows ties)	RANK or DENSE_RANK
Get top N (including ties)	RANK
Grouping (like quartiles)	NTILE

Do Window Functions Affect Query Performance?

Yes, window functions require additional computational resources, but impact depends on multiple factors:

Factors Affecting Performance:

1. Data volume: More data rows means more sorting and calculation overhead
2. Number of windows: Multiple different window definitions require multiple sorts
3. Index design: Missing appropriate indexes leads to extensive sorting operations
4. Frame range: ROWS UNBOUNDED requires scanning more data

Optimization Tips:

1. Create indexes for PARTITION BY and ORDER BY columns
2. Use WHERE or CTE to reduce data volume before using window functions
3. Avoid too many different window definitions in the same query
4. Use ROWS instead of RANGE (ROWS performs better)

-- Better performing approach
CREATE INDEX IX_Covering ON Sales(RepId, Month) INCLUDE (Amount);

WITH FilteredSales AS (
    SELECT * FROM Sales WHERE Year = 2024
)
SELECT *, SUM(Amount) OVER (PARTITION BY RepId ORDER BY Month)
FROM FilteredSales;

---

Further Reading

• SQL Advanced Techniques Practical Tips
• SQL Stored Procedure Tutorial
• SQL Performance Tuning Complete Guide
• SQL Functions Complete Guide
• SQL Subquery Complete Tutorial

---

References

1. Microsoft SQL Server Window Functions Official Documentation
2. SQL Window Functions Standard Specification
3. Advanced SQL Query Design Best Practices