SQL Window Functions: A Quick Review With Examples

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:

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:

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:

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:

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:

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:

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:

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!👋