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, which combine rows into a single output, window functions return a result for each row while allowing calculations to consider other rows in the defined window.

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:

1. ROW_NUMBER()

The ROW_NUMBER() function assigns a unique number to each row based on the order defined in the ORDER BY clause within the OVER statement, optionally partitioned by the PARTITION BY clause.

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:

2. RANK()

The RANK() function assigns a rank to each row based on the ORDER BY clause in the OVER statement. Rows with the same value receive the same rank, with gaps in the ranking for duplicate values.

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:

Key Feature:

• For 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:

Key Feature:

• For 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:

5. LEAD()

LEAD() retrieves the value from the next row as determined by the ORDER BY clause in the OVER statement, within the same partition if specified.

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:

6. LAG()

LAG() retrieves the value from the previous row as determined by the ORDER BY clause in the OVER statement, within the same partition if specified.

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:

Conclusion

SQL window functions like ROW_NUMBER(), RANK(), DENSE_RANK(), NTILE(), LEAD(), and LAG() provide powerful ways to analyze data by performing calculations across rows within a defined window.

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!

Explore more

Luca LiuFollow

Hello there! 👋 I'm Luca, a Business Intelligence Developer with passion for all things data. Proficient in Python, SQL, Power BI, Tableau, SAP Business Objects.

Thank you for taking the time to explore data-related insights with me. I appreciate your engagement.

🚀 Connect with me on LinkedIn