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 aggregate rows into a single output; They return a result for each row while maintaining the context of the data set.

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 will use the following sales table to illustrate the window’s functions:

1.ROW_NUMBER()

The ROW_NUMBER() function assigns a unique number to each row within a section, ordered by a specific column.

a 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;

a result:

2. sort()

The RANK() function assigns a rank to each row within a section. Rows with the same values ​​get the same order, and the next order is skipped.

a task: Ranking of sales within each region by amount (from highest to lowest).

SELECT SalesID, Region, Amount,
       RANK() OVER (PARTITION BY Region ORDER BY Amount DESC) AS Rank
FROM Sales;

a result:

Main advantage:

• For the North region, both sum = 800 rows share second place.

• The next rank is skipped (for example, rank 3 is missing) and jumps to rank 4.

3.DENSE_RANK()

The DENSE_RANK() function assigns ranks like RANK(), but does not skip ranks after links.

a task: Assign density 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;

a result:

Main advantage:

• For the North region, both sum = 800 rows share second place.

• The next rank is 3, without skipping ranks.

4. ntel()

The NTILE() function divides rows into a specified number of approximately equal groups.

a 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;

a result:

5. Lead()

LEAD() retrieves the value from the next row within the same partition.

a task: Compare each sale amount to the next sale amount, sorted by date of sale.

SELECT SalesID, Amount, 
       LEAD(Amount) OVER (ORDER BY SaleDate) AS NextAmount
FROM Sales;

a result:

6. lag()

LAG() Retrieves the value from the previous row within the same section.

a taskCompare each sale amount with the previous sale amount, sorted by date of sale.

SELECT SalesID, Amount, 
       LAG(Amount) OVER (ORDER BY SaleDate) AS PrevAmount
FROM Sales;

a result:

conclusion

SQL window functions such as 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 to each row.
• RANK() and DENSE_RANK() They differ in how they handle relationships (skipping vs. not skipping).
• NTILE() Useful for dividing rows into statistical groups.
• LEAD() and LAG() Allow comparisons with adjacent rows.

By mastering these functions, you can handle complex analytics and classification tasks effectively!

Thank you for taking the time to explore data insights with me. I appreciate your participation. If you found this information helpful, I invite you to follow or connect with me on LinkedIn. Happy exploring!👋