This content originally appeared on HackerNoon and was authored by Luca Liu
\
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 ROWNUMBER(), RANK(), DENSERANK(), NTILE(), LEAD(), and LAG() provide powerful ways to analyze data within partitions.
\ Key Takeaways:
ROW_NUMBER()assigns a unique identifier for each row.RANK()andDENSE_RANK()differ in how they handle ties (skipping vs. no skipping).NTILE()is useful for dividing rows into statistic groups.LEAD()andLAG()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!👋
This content originally appeared on HackerNoon and was authored by Luca Liu
Luca Liu | Sciencx (2025-01-05T17:00:04+00:00) SQL Window Functions: A Quick Review With Examples. Retrieved from https://www.scien.cx/2025/01/05/sql-window-functions-a-quick-review-with-examples/
Please log in to upload a file.
There are no updates yet.
Click the Upload button above to add an update.