Introduction Window functions are a powerful feature in SQL used to perform calculations across a set of rows related to the current row. Unlike aggregate functions, window functions do not group rows into a single output; they return a result for each row while maintaining the context of the dataset. In this article, we’ll explore some commonly used SQL window functions (ROW_NUMBER(), RANK(), DENSE_RANK(), NTILE(), LEAD(), and LAG()) with examples. Sample Table: Sales Data We’ll use the following Sales table to demonstrate window functions: SalesID

CustomerID

Product

Region

Amount

SaleDate



1

101

Laptop

North

1200

2023-01-05



2

102

Tablet

North

800

2023-02-15



3

103

Phone

North

800

2023-03-10



4

104

Tablet

North

500

2023-04-01



5

105

Laptop

South

1300

2023-05-05



6

106

Tablet

South

700

2023-06-20



7

107

Phone

West

900

2023-07-15



8

108

Laptop

East

1300

2023-08-10 1. ROW_NUMBER() The ROW_NUMBER() function assigns a unique number to each row within a partition, ordered by a specified column. Task: Assign a unique row number to each sale within a region based on the sale amount (highest to lowest). SELECT SalesID, Region, Amount,
       ROW_NUMBER() OVER (PARTITION BY Region ORDER BY Amount DESC) AS RowNum
FROM Sales; Result: SalesID

Region

Amount

RowNum



1

North

1200

1



2

North

800

2



3

North

800

3



4

North

500

4



5

South

1300

1



6

South

700

2



7

West

900

1



8

East

1300

1 2. RANK() The RANK() function assigns a rank to each row within a partition. Rows with the same values receive the same rank, and the next rank is skipped. Task: Rank sales within each region by amount (highest to lowest). SELECT SalesID, Region, Amount,
       RANK() OVER (PARTITION BY Region ORDER BY Amount DESC) AS Rank
FROM Sales; Result: SalesID

Region

Amount

Rank



1

North

1200

1



2

North

800

2



3

North

800

2



4

North

500

4



5

South

1300

1



6

South

700

2



7

West

900

1



8

East

1300

1 Key Feature: For the North region, both Amount = 800 rows share rank 2. The next rank is skipped (i.e., rank 3 is missing) and jumps to 4. 3. DENSE_RANK() The DENSE_RANK() function assigns ranks like RANK(), but it doesn’t skip ranks after ties. Task: Assign dense ranks to sales within each region by amount (highest to lowest). SELECT SalesID, Region, Amount,
       DENSE_RANK() OVER (PARTITION BY Region ORDER BY Amount DESC) AS DenseRank
FROM Sales; Result: SalesID

Region

Amount

DenseRank



1

North

1200

1



2

North

800

2



3

North

800

2



4

North

500

3



5

South

1300

1



6

South

700

2



7

West

900

1



8

East

1300

1 Key Feature: For the North region, both Amount = 800 rows share rank 2. The next rank is 3, with no skipping of ranks. 4. NTILE() NTILE() divides rows into a specified number of approximately equal groups. Task: Divide all sales into 4 groups based on Amount in descending order. SELECT SalesID, Amount,
       NTILE(4) OVER (ORDER BY Amount DESC) AS Quartile
FROM Sales; Result: SalesID

Amount

Quartile



5

1300

1



8

1300

1



1

1200

2



7

900

2



2

800

3



3

800

3



4

500

4



6

700

4 5. LEAD() LEAD() retrieves the value from the next row within the same partition. Task: Compare each sale amount to the next sale amount, ordered by SaleDate. SELECT SalesID, Amount, 
       LEAD(Amount) OVER (ORDER BY SaleDate) AS NextAmount
FROM Sales; Result: SalesID

Amount

NextAmount



1

1200

800



2

800

800



3

800

500



4

500

1300



5

1300

700



6

700

900



7

900

1300



8

1300

NULL 6. LAG() LAG() retrieves the value from the previous row within the same partition. Task: Compare each sale amount to the previous sale amount, ordered by SaleDate. SELECT SalesID, Amount, 
       LAG(Amount) OVER (ORDER BY SaleDate) AS PrevAmount
FROM Sales; Result: SalesID

Amount

PrevAmount



1

1200

NULL



2

800

1200



3

800

800



4

500

800



5

1300

500



6

700

1300



7

900

700



8

1300

900 Conclusion SQL window functions like ROW_NUMBER(), RANK(), DENSE_RANK(), NTILE(), LEAD(), and LAG() provide powerful ways to analyze data within partitions. Key Takeaways: ROW_NUMBER() assigns a unique identifier for each row.
RANK() and DENSE_RANK() differ in how they handle ties (skipping vs. no skipping).
NTILE() is useful for dividing rows into statistic groups.
LEAD() and LAG() allow comparisons with adjacent rows. By mastering these functions, you can handle complex analytics and ranking tasks effectively! Thank you for taking the time to explore data-related insights with me. I appreciate your engagement. If you find this information helpful, I invite you to follow me or connect with me on LinkedIn. Happy exploring!👋 Introduction Window functions are a powerful feature in SQL used to perform calculations across a set of rows related to the current row. Unlike aggregate functions, window functions do not group rows into a single output; they return a result for each row while maintaining the context of the dataset. In this article, we’ll explore some commonly used SQL window functions ( ROW_NUMBER() , RANK() , DENSE_RANK() , NTILE() , LEAD() , and LAG() ) with examples. ROW_NUMBER() RANK() DENSE_RANK() NTILE() LEAD() LAG() Sample Table: Sales Data We’ll use the following Sales table to demonstrate window functions: SalesID

CustomerID

Product

Region

Amount

SaleDate



1

101

Laptop

North

1200

2023-01-05



2

102

Tablet

North

800

2023-02-15



3

103

Phone

North

800

2023-03-10



4

104

Tablet

North

500

2023-04-01



5

105

Laptop

South

1300

2023-05-05



6

106

Tablet

South

700

2023-06-20



7

107

Phone

West

900

2023-07-15



8

108

Laptop

East

1300

2023-08-10 SalesID

CustomerID

Product

Region

Amount

SaleDate 1

101

Laptop

North

1200

2023-01-05 2

102

Tablet

North

800

2023-02-15 3

103

Phone

North

800

2023-03-10 4

104

Tablet

North

500

2023-04-01 5

105

Laptop

South

1300

2023-05-05 6

106

Tablet

South

700

2023-06-20 7

107

Phone

West

900

2023-07-15 8

108

Laptop

East

1300

2023-08-10 SalesID CustomerID Product Region Amount SaleDate SalesID SalesID CustomerID CustomerID Product Product Region Region Amount Amount SaleDate SaleDate 1 101 Laptop North 1200 2023-01-05 1 1 101 101 Laptop Laptop North North 1200 1200 2023-01-05 2023-01-05 2 102 Tablet North 800 2023-02-15 2 2 102 102 Tablet Tablet North North 800 800 2023-02-15 2023-02-15 3 103 Phone North 800 2023-03-10 3 3 103 103 Phone Phone North North 800 800 2023-03-10 2023-03-10 4 104 Tablet North 500 2023-04-01 4 4 104 104 Tablet Tablet North North 500 500 2023-04-01 2023-04-01 5 105 Laptop South 1300 2023-05-05 5 5 105 105 Laptop Laptop South South 1300 1300 2023-05-05 2023-05-05 6 106 Tablet South 700 2023-06-20 6 6 106 106 Tablet Tablet South South 700 700 2023-06-20 2023-06-20 7 107 Phone West 900 2023-07-15 7 7 107 107 Phone Phone West West 900 900 2023-07-15 2023-07-15 8 108 Laptop East 1300 2023-08-10 8 8 108 108 Laptop Laptop East East 1300 1300 2023-08-10 2023-08-10 1. ROW_NUMBER() The ROW_NUMBER() function assigns a unique number to each row within a partition, ordered by a specified column. Task : Assign a unique row number to each sale within a region based on the sale amount (highest to lowest). Task SELECT SalesID, Region, Amount,
       ROW_NUMBER() OVER (PARTITION BY Region ORDER BY Amount DESC) AS RowNum
FROM Sales; SELECT SalesID, Region, Amount,
       ROW_NUMBER() OVER (PARTITION BY Region ORDER BY Amount DESC) AS RowNum
FROM Sales; Result : Result SalesID

Region

Amount

RowNum



1

North

1200

1



2

North

800

2



3

North

800

3



4

North

500

4



5

South

1300

1



6

South

700

2



7

West

900

1



8

East

1300

1 SalesID

Region

Amount

RowNum 1

North

1200

1 2

North

800

2 3

North

800

3 4

North

500

4 5

South

1300

1 6

South

700

2 7

West

900

1 8

East

1300

1 SalesID Region Amount RowNum SalesID SalesID Region Region Amount Amount RowNum RowNum 1 North 1200 1 1 1 North North 1200 1200 1 1 2 North 800 2 2 2 North North 800 800 2 2 3 North 800 3 3 3 North North 800 800 3 3 4 North 500 4 4 4 North North 500 500 4 4 5 South 1300 1 5 5 South South 1300 1300 1 1 6 South 700 2 6 6 South South 700 700 2 2 7 West 900 1 7 7 West West 900 900 1 1 8 East 1300 1 8 8 East East 1300 1300 1 1 2. RANK() The RANK() function assigns a rank to each row within a partition. Rows with the same values receive the same rank, and the next rank is skipped. Task : Rank sales within each region by amount (highest to lowest). Task SELECT SalesID, Region, Amount,
       RANK() OVER (PARTITION BY Region ORDER BY Amount DESC) AS Rank
FROM Sales; SELECT SalesID, Region, Amount,
       RANK() OVER (PARTITION BY Region ORDER BY Amount DESC) AS Rank
FROM Sales; Result : Result SalesID

Region

Amount

Rank



1

North

1200

1



2

North

800

2



3

North

800

2



4

North

500

4



5

South

1300

1



6

South

700

2



7

West

900

1



8

East

1300

1 SalesID

Region

Amount

Rank 1

North

1200

1 2

North

800

2 3

North

800

2 4

North

500

4 5

South

1300

1 6

South

700

2 7

West

900

1 8

East

1300

1 SalesID Region Amount Rank SalesID SalesID Region Region Amount Amount Rank Rank 1 North 1200 1 1 1 North North 1200 1200 1 1 2 North 800 2 2 2 North North 800 800 2 2 3 North 800 2 3 3 North North 800 800 2 2 4 North 500 4 4 4 North North 500 500 4 4 5 South 1300 1 5 5 South South 1300 1300 1 1 6 South 700 2 6 6 South South 700 700 2 2 7 West 900 1 7 7 West West 900 900 1 1 8 East 1300 1 8 8 East East 1300 1300 1 1 Key Feature : Key Feature For the North region, both Amount = 800 rows share rank 2. For the North region, both Amount = 800 rows share rank 2. The next rank is skipped (i.e., rank 3 is missing) and jumps to 4. The next rank is skipped (i.e., rank 3 is missing) and jumps to 4. 3. DENSE_RANK() The DENSE_RANK() function assigns ranks like RANK(), but it doesn’t skip ranks after ties. Task : Assign dense ranks to sales within each region by amount (highest to lowest). Task SELECT SalesID, Region, Amount,
       DENSE_RANK() OVER (PARTITION BY Region ORDER BY Amount DESC) AS DenseRank
FROM Sales; SELECT SalesID, Region, Amount,
       DENSE_RANK() OVER (PARTITION BY Region ORDER BY Amount DESC) AS DenseRank
FROM Sales; Result : Result SalesID

Region

Amount

DenseRank



1

North

1200

1



2

North

800

2



3

North

800

2



4

North

500

3



5

South

1300

1



6

South

700

2



7

West

900

1



8

East

1300

1 SalesID

Region

Amount

DenseRank 1

North

1200

1 2

North

800

2 3

North

800

2 4

North

500

3 5

South

1300

1 6

South

700

2 7

West

900

1 8

East

1300

1 SalesID Region Amount DenseRank SalesID SalesID Region Region Amount Amount DenseRank DenseRank 1 North 1200 1 1 1 North North 1200 1200 1 1 2 North 800 2 2 2 North North 800 800 2 2 3 North 800 2 3 3 North North 800 800 2 2 4 North 500 3 4 4 North North 500 500 3 3 5 South 1300 1 5 5 South South 1300 1300 1 1 6 South 700 2 6 6 South South 700 700 2 2 7 West 900 1 7 7 West West 900 900 1 1 8 East 1300 1 8 8 East East 1300 1300 1 1 Key Feature : Key Feature For the North region, both Amount = 800 rows share rank 2. For the North region, both Amount = 800 rows share rank 2. The next rank is 3, with no skipping of ranks. The next rank is 3, with no skipping of ranks. 4. NTILE() NTILE() divides rows into a specified number of approximately equal groups. Task : Divide all sales into 4 groups based on Amount in descending order. Task SELECT SalesID, Amount,
       NTILE(4) OVER (ORDER BY Amount DESC) AS Quartile
FROM Sales; SELECT SalesID, Amount,
       NTILE(4) OVER (ORDER BY Amount DESC) AS Quartile
FROM Sales; Result : Result SalesID

Amount

Quartile



5

1300

1



8

1300

1



1

1200

2



7

900

2



2

800

3



3

800

3



4

500

4



6

700

4 SalesID

Amount

Quartile 5

1300

1 8

1300

1 1

1200

2 7

900

2 2

800

3 3

800

3 4

500

4 6

700

4 SalesID Amount Quartile SalesID SalesID Amount Amount Quartile Quartile 5 1300 1 5 5 1300 1300 1 1 8 1300 1 8 8 1300 1300 1 1 1 1200 2 1 1 1200 1200 2 2 7 900 2 7 7 900 900 2 2 2 800 3 2 2 800 800 3 3 3 800 3 3 3 800 800 3 3 4 500 4 4 4 500 500 4 4 6 700 4 6 6 700 700 4 4 5. LEAD() LEAD() retrieves the value from the next row within the same partition. Task : Compare each sale amount to the next sale amount, ordered by SaleDate. Task SELECT SalesID, Amount, 
       LEAD(Amount) OVER (ORDER BY SaleDate) AS NextAmount
FROM Sales; SELECT SalesID, Amount, 
       LEAD(Amount) OVER (ORDER BY SaleDate) AS NextAmount
FROM Sales; Result : Result SalesID

Amount

NextAmount



1

1200

800



2

800

800



3

800

500



4

500

1300



5

1300

700



6

700

900



7

900

1300



8

1300

NULL SalesID

Amount

NextAmount 1

1200

800 2

800

800 3

800

500 4

500

1300 5

1300

700 6

700

900 7

900

1300 8

1300

NULL SalesID Amount NextAmount SalesID SalesID Amount Amount NextAmount NextAmount 1 1200 800 1 1 1200 1200 800 800 2 800 800 2 2 800 800 800 800 3 800 500 3 3 800 800 500 500 4 500 1300 4 4 500 500 1300 1300 5 1300 700 5 5 1300 1300 700 700 6 700 900 6 6 700 700 900 900 7 900 1300 7 7 900 900 1300 1300 8 1300 NULL 8 8 1300 1300 NULL NULL 6. LAG() LAG() retrieves the value from the previous row within the same partition. LAG() Task : Compare each sale amount to the previous sale amount, ordered by SaleDate. Task SELECT SalesID, Amount, 
       LAG(Amount) OVER (ORDER BY SaleDate) AS PrevAmount
FROM Sales; SELECT SalesID, Amount, 
       LAG(Amount) OVER (ORDER BY SaleDate) AS PrevAmount
FROM Sales; Result : Result SalesID

Amount

PrevAmount



1

1200

NULL



2

800

1200



3

800

800



4

500

800



5

1300

500



6

700

1300



7

900

700



8

1300

900 SalesID

Amount

PrevAmount 1

1200

NULL 2

800

1200 3

800

800 4

500

800 5

1300

500 6

700

1300 7

900

700 8

1300

900 SalesID Amount PrevAmount SalesID SalesID Amount Amount PrevAmount PrevAmount 1 1200 NULL 1 1 1200 1200 NULL NULL 2 800 1200 2 2 800 800 1200 1200 3 800 800 3 3 800 800 800 800 4 500 800 4 4 500 500 800 800 5 1300 500 5 5 1300 1300 500 500 6 700 1300 6 6 700 700 1300 1300 7 900 700 7 7 900 900 700 700 8 1300 900 8 8 1300 1300 900 900 Conclusion SQL window functions like ROW_NUMBER(), RANK(), DENSE_RANK(), NTILE(), LEAD(), and LAG() provide powerful ways to analyze data within partitions. Key Takeaways: Key Takeaways: ROW_NUMBER() assigns a unique identifier for each row. RANK() and DENSE_RANK() differ in how they handle ties (skipping vs. no skipping). NTILE() is useful for dividing rows into statistic groups. LEAD() and LAG() allow comparisons with adjacent rows. ROW_NUMBER() assigns a unique identifier for each row. ROW_NUMBER() RANK() and DENSE_RANK() differ in how they handle ties (skipping vs. no skipping). RANK() DENSE_RANK() NTILE() is useful for dividing rows into statistic groups. NTILE() LEAD() and LAG() allow comparisons with adjacent rows. LEAD() LAG() By mastering these functions, you can handle complex analytics and ranking tasks effectively! Thank you for taking the time to explore data-related insights with me. I appreciate your engagement. If you find this information helpful, I invite you to follow me or connect with me on LinkedIn . Happy exploring!👋 LinkedIn

How to Get Responses From Local LLM Models With Python

How to Use Python to Publish an Article to Dev.to

Read My Stories

Too Long; Didn't Read

Make resilience your competitive advantage

SQL Window Functions: A Quick Review With Examples

About Author

Comments

TOPICS

THIS ARTICLE WAS FEATURED IN

Related Stories

Untitled Story

A Quick Guide to Listing All Measures in Power BI via DAX Studio

10 Ways to Optimize Your Database

10 Ways to Reduce Data Loss and Potential Downtime Of Your Database

10 Principles of Proper Database Benchmarking

10 Minute Guide to Fixing Damaged SQL Databases - No Recovery Required!

A Quick Guide to Listing All Measures in Power BI via DAX Studio

10 Ways to Optimize Your Database

10 Ways to Reduce Data Loss and Potential Downtime Of Your Database

10 Principles of Proper Database Benchmarking

10 Minute Guide to Fixing Damaged SQL Databases - No Recovery Required!

Light-Mode

Classic

Newspaper

Dark-Mode

Neon Noir

Minty

HN StartUps