SQL Server Window Functions

Window functions are one of those SQL features that I didn’t
use in my early career. Perhaps because I was confused about how to use them properly in a way that saves time for both me and SQL queries.
But while you are seeking to optimize your app and take the performance to another level, window functions are an essential feature you can’t ignore at all. It can help you perform analytical queries across a set of related rows, without losing the rows themselves.

Photo by Ján Jakub Naništa on Unsplash

In this article, I will pave the way and explain window functions in detail and provide fully detailed examples for each function.

Before we dive deep into the coding area, let us first take a quick look at the different types of window functions. I split them into groups of four categories. I will walk through them category by category, function by function.

Window Functions

To test every function, we need a database to use for demonstration. So, go ahead and run the script below inside your SQL Server Management Studio, and let’s start.

create database WindowFunctionsDB;
go

USE WindowFunctionsDB;
go

create table Houses (
    Id INT identity(1,1) primary key,
    Specification nvarchar(200),
    CityId int,
    Price decimal(10,2),
    PurchaseDate date
);
go

insert into Houses (Specification, CityId, Price, PurchaseDate)
values
(N'3BR Apartment', 1, 120000.00, '2023-01-10'),
(N'2BR Apartment', 1, 110000.00, '2023-01-15'),
(N'Villa', 2, 250000.00, '2023-02-01'),
(N'Penthouse', 3, 450000.00, '2023-03-05'),
(N'1BR Studio', 2, 90000.00, '2023-01-20'),
(N'2BR Apartment', 1, 110000.00, '2023-01-22'),
(N'3BR Townhouse', 2, 180000.00, '2023-03-10'),
(N'4BR Villa', 3, 370000.00, '2023-02-20'),
(N'1BR Studio', 3, 95000.00, '2023-01-18'),
(N'Penthouse', 3, 450000.00, '2023-03-06'),
(N'3BR Apartment', 1, 130000.00, '2023-04-01'),
(N'2BR Apartment', 2, 105000.00, '2023-01-25'),
(N'Villa', 1, 200000.00, '2023-03-12'),
(N'1BR Apartment', 3, 98000.00, '2023-02-10'),
(N'4BR Villa', 2, 240000.00, '2023-03-15'),
(N'2BR Townhouse', 1, 115000.00, '2023-02-28'),
(N'3BR Townhouse', 3, 185000.00, '2023-04-05'),
(N'Villa', 2, 250000.00, '2023-04-07'),
(N'1BR Studio', 1, 95000.00, '2023-01-11'),
(N'2BR Apartment', 3, 110000.00, '2023-02-25');

Window functions can be applied using the code below.

<function_name>(<expression>) OVER (
  [PARTITION BY ...]
  [ORDER BY ...]
)

OVER: specifies that the function is a window function; if we didn’t use OVER, SQL Server would deal with the function as a normal function only.

PARTITION BY: is the first parameter, we use it to split the data into groups. The window function will be applied to each group separately.

ORDER BY: It applies ordering of the row for each group, either in descending or ascending order.

Note that some parameters are optional, and not all functions support all of them. For example, expression supported by NTILE functions, but it is not supported by RANK. Additionally, [ROWS | RANGE BETWEEN] is supported by MAX and MIN, but not by ROW_NUMBER.

Ranking Functions

ROW_NUMBER is a window function that assigns a sequential integer starting from 1 to reach the last row, and it is incremented by one; note that each partition starts from one. In our example, we partition by City Id, then each City Id group will start from 1 to reach the last row in that partition.

SELECT
    Id,
    CityId,
    Price,
    ROW_NUMBER() OVER (PARTITION BY CityId ORDER BY Price DESC) AS RowNum
FROM Houses
WHERE CityId = 1

Output:

ROW_NUMBER result

RANK_NUMBER is a window function that gives a position to the row within each partition based on the ORDER BY clause. Unlike ROW_NUMBER, the RANK function gives the same integer value (position) to rows with the same value, also it leaves a gap between row positioning. For example, if two rows get the position 1, the third row will get the position 3, not 2.

SELECT
     Id,
    CityId,
    Price,
    RANK() OVER (PARTITION BY CityId ORDER BY Price DESC) AS Rank
FROM Houses
WHERE CityId = 3

Output:

RANK result

RANK_DENSE is a window function that works exactly like RANK, but the only difference is that RANK_DENSE doesn’t leave a gap between rows. For example, if the first two rows have the same value, they will get the same position 1, while the third value will get the position 2.

SELECT
      Id,
    CityId,
    Price,
    DENSE_RANK() OVER (PARTITION BY CityId ORDER BY Price DESC) AS DenseRank
FROM Houses
WHERE CityId = 3

Output:

DENSE_RANK result

NTILE is a window function that splits the data into n groups. For example, if I have 6 rows, and I want to split them into 4, the first two rows will get value 1, and the second two rows will get value 2, and the third and fourth will get 3 and 4, respectively.

SELECT
    Id,
    CityId,
    Price,
    NTILE(4) OVER (PARTITION BY CityId ORDER BY Price DESC) AS PriceQuartile
FROM Houses
WHERE CityId = 2

Output:

NTILE result

Aggregate Functions

In Aggregate functions, we will deal with an extra parameter, which is ROWS BETWEEN or RANGE BETWEEN. These two clauses define the frame of data we want to get; while they look similar to each other, they are totally different.

ROWS BETWEEN deals with rows and their physical position, while the RANGE BETWEEN deals with the value of the rows.

You can specify a frame for both of them by using:

1. UNBOUNDED PRECEDING: from the beginning of the partition.
2. N PRECEDING: specific number before the current row.
3. CURRENT ROW: current row.
4. N FOLLOWING: specific number after the current row.
5. UNBOUNDED FOLLOWING: to the end of the partition.

MIN is an aggregate window function that returns the minimum value of the row. Usually, there is only one minimum value for each partition, but since we are using ROWS BETWEEN, the minimum value of each row will vary depending on the condition. For example, we’re using 1 PRECEDING and 1 FOLLOWING; the minimum value of a row will be the minimum between 3 values: the current one, the previous one, and the next one.

Get Mustafa Jalloul’s stories in your inbox

Join Medium for free to get updates from this writer.

Remember me for faster sign in

You can change the condition after ROWS BETWEEN to see different results.

SELECT
    Id,
    CityId,
    Price,
    MIN(Price) OVER (
        PARTITION BY CityId
        ORDER BY Price
        ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING
    ) AS Minimum
FROM Houses
WHERE CityId = 1

Output:

MIN result

MAX is another aggregate function; it works exactly like MIN, but instead it gets the maximum value.

SELECT
    Id,
 Specification,
    CityId,
    Price,
    MAX(Price) OVER (
        PARTITION BY CityId
        ORDER BY Price
  ROWS BETWEEN UNBOUNDED PRECEDING AND 2 FOLLOWING
    ) AS Maximum
FROM Houses
WHERE CityId = 1

Output:

MAX result

SUM is another aggregate window function that calculates the total of a specific numeric column within the partition. In the below script, I add ROWS BETWEEN CURRENT ROW AND 1 FOLLOWING; this means that the Total of the row is the summation of the numeric column of this row plus the next following row.

SELECT
    Id,
 Specification,
    CityId,
    Price,
    SUM(Price) OVER (
        PARTITION BY CityId 
        ORDER BY Price 
  ROWS BETWEEN CURRENT ROW AND 1 FOLLOWING 
    ) AS Total
FROM Houses
WHERE CityId = 1

Output:

SUM result

AVG is the fourth aggregate window function. Simple from its name, it is clear what it does. It calculates the average of a specific numeric value. The value will change by changing the frame specified after ROWS BETWEEN.

SELECT
    Id,
 Specification,
    CityId,
    Price,
    AVG(Price) OVER (
        PARTITION BY CityId
        ORDER BY Price
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS Average
FROM Houses
WHERE CityId = 1

Output:

AVG result

COUNT is an aggregate window function used to count the number of rows within the partition. For example, in the script below, we are counting the number of rows between the beginning of each frame to the next 2 rows after the current.

SELECT
    Id,
 Specification,
    CityId,
    Price,
    COUNT(*) OVER (
        PARTITION BY CityId
        ORDER BY Price
  ROWS BETWEEN UNBOUNDED PRECEDING AND 2 FOLLOWING
    ) AS [Count]
FROM Houses
WHERE CityId = 2

Output:

COUNT result

Distribution Functions

CUME_DIST calculates the cumulative distribution for the row based on the count of the rows within the partition.

SELECT
    Id,
    CityId,
    Price,
    CUME_DIST() OVER (
        PARTITION BY CityId
        ORDER BY Price
    ) AS CumeDist

FROM Houses
WHERE CityId = 1

Output:

CUME_DIST result

To understand more about how CUME_DIST works, you can check the script below.

SELECT
    Id,
    CityId,
    Price,
 COUNT(*) OVER (
    PARTITION BY CityId
    ORDER BY Price
    RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS [RowCount],
    COUNT(*) OVER (
        PARTITION BY CityId
    ) AS TotalRowsInCity,
 'RowCount / TotalRowsInCity' AS Formula,
    CAST(
      COUNT(*) OVER (
        PARTITION BY CityId
        ORDER BY Price
        RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
      ) AS FLOAT
    ) 
    / 
    COUNT(*) OVER (
        PARTITION BY CityId
    ) AS CountRatio,

    CUME_DIST() OVER (
        PARTITION BY CityId
        ORDER BY Price
    ) AS CumeDist
FROM Houses
WHERE CityId = 1

Output:

CUME_DIST result 2

PERCENT_RANK is a window function that calculates the relative ranks of the rows between 0 and 1. The formula to calculate PERCENT_RANK is (RANK — 1) / (TotalRowsWithinPartition — 1); the reason behind adding -1 is to make sure that the value is always between 0 and 1, since if we didn’t add it, the lower rank would not be equal to 0 ever.

SELECT
    Id,
    Price,
    CityId,
    'PercentRank = (RANK - 1) / (TotalRowsWithinPartition - 1)' AS PercentRankFormula,
    RANK() OVER (PARTITION BY CityId ORDER BY Price) AS Rank,
    (RANK() OVER ( PARTITION BY CityId ORDER BY Price) - 1) * 1.0 / (COUNT(*) OVER (PARTITION BY CityId) - 1) AS PercentRank,
    PERCENT_RANK() OVER ( PARTITION BY CityId ORDER BY Price) PR
FROM Houses;

Output:

PERCENT_RANK result

Value Access Functions

FIRST_VALUE is a value access window function that returns the first value within a partition.

SELECT
  Id,
  Specification,
  CityId,
  PurchaseDate,
  Price,
  FIRST_VALUE(Price) OVER (
      PARTITION BY CityId
      ORDER BY PurchaseDate
  ) AS FirstPurchasePrice
FROM Houses
WHERE CityId = 1

Output:

FIRST_VALUE result

LAST_VALUE is a value access window function that returns the last value within a partition.

Note: In LAST_VALUE, we should specify the frame of the result. The reason behind that goes back to the default frame, which is ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. It is a perfect fit for FIRST_VALUE because it is unbounded preceding, so it has access to the first value. But since the following isn’t unbounded, we will get a wrong result in LAST_VALUE; for that reason, we should specify ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING to gain access to the last value and get the correct result.

SELECT
    Id,
    Specification,
    CityId,
    PurchaseDate,
    Price,
    LAST_VALUE(Price) OVER (
        PARTITION BY CityId
        ORDER BY PurchaseDate
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ) AS LastHiredSalary
FROM Houses
where CityId = 1

Output:

LAST_VALUE result

LEAD is a value access window function that returns the value of a column of the next row.

SELECT
    Id,
    Specification,
    CityId,
    Price,
    PurchaseDate,
    LEAD(Price) OVER (
        PARTITION BY CityId
        ORDER BY PurchaseDate
    ) AS NextSalary
FROM Houses
WHERE CityId = 1

Output:

LEAD result

LAG is a value access window function that returns the value of a column of the previous row.

SELECT
    Id,
    Specification,
    CityId,
    Price,
    PurchaseDate,
    LAG(Price) OVER (
        PARTITION BY CityId
        ORDER BY PurchaseDate
    ) AS PreviousSalary
FROM Houses
WHERE CityId = 1

Output:

LAG result

Conclusion

All kinds of businesses rely on data to grow. But unprocessed raw data is useless if we can’t extract useful information from it. Window functions are a powerful feature in SQL that help you process this raw input into meaningful information, enabling advanced analytics within your queries.

After finishing this article, try to change the frame bounds and take a look at a different result. Since every problem and business requirement will need different conditions and a frame to be solved.