Mastering SQL Window Functions

in my work, I even have written countless SQL queries to extract insights from data. It’s at all times a difficult task since it’s not only essential to write down efficient queries, but additionally easy enough to keep up over time.

With each recent problem comes a brand new lesson, and recently, I’ve been diving into SQL window functions. These powerful tools are incredibly useful when you want to perform calculations across a set of rows without losing the granularity of individual records. 

In this text, I’ll break down SQL window functions step-by-step. They may appear complex or unintuitive at first, but when you understand how they work, you’ll see how indispensable they might be. Are you ready? Let’s dive in and master them together!

Table of contents

• Why do we’d like Window Functions?
• Syntax of Window Function 
• 4 Easy Examples

Why do we’d like Window Functions?

To grasp the facility of Window Functions, let’s start with a straightforward example. Imagine we’ve got a table containing six orders from an e-commerce website. Each row includes the order id, the date, the product, its brand and price.

Let’s suppose that we would like to calculate the whole price for every brand. Using the GROUP BY clause, we are able to write a question like this:

SELECT 
      brand, 
      SUM(price) as total_price 
FROM Orders 
GROUP BY brand

This returns a result where each row represents one brand, together with the whole price of all orders under that brand. 

|brand  |total_price|
|-------|-----------|
|carpisa|30         |
|nike   |175        |
|parfois|25         |
|zara   |65         |

This aggregation removes the small print of individual orders, for the reason that output only includes one row for brand. What if we would like to maintain all the unique rows and add the whole price for every brand as an additional field? 

Through the use of SUM(price) OVER (PARTITION BY brand), we are able to calculate the whole price for every brand without collapsing the rows:

SELECT 
    order_id,
    date,
    product,
    brand,
    price,
    SUM(price) OVER (PARTITION BY brand) as total_price
FROM Orders

We now have obtained a result like this:

|order_id|date      |product|brand  |price|total_price|
|--------|----------|-------|-------|-----|-----------|
|6       |2025/05/01|bag    |carpisa|30   |30         |
|1       |2024/02/01|shoes  |nike   |90   |175        |
|3       |2024/06/01|shoes  |nike   |85   |175        |
|5       |2025/04/01|bag    |parfois|25   |25         |
|2       |2024/05/01|dress  |zara   |50   |65         |
|4       |2025/01/01|t-shirt|zara   |15   |65         |

This question returns all six rows, preserving every individual order, and adds a brand new column showing the whole price per brand. For instance, the order with brand Carpisa shows a complete of 30, because it’s the one Carpisa order, the 2 orders from Nike show 175 (90+85), and so forth. 

It’s possible you’ll notice that the table is not any longer ordered by order_id. That’s since the window function partitions by brand, and SQL doesn’t guarantee row order unless explicitly specified. To revive the unique order, we’d like to easily add an ORDER BY clause:

SELECT 
    order_id,
    date,
    product,
    brand,
    price,
    SUM(price) OVER (PARTITION BY brand) as total_price
FROM Orders
ORDER BY order_id

Finally, we’ve got the output containing all of the required details:

|order_id|date      |product|brand  |price|total_price|
|--------|----------|-------|-------|-----|-----------|
|1       |2024/02/01|shoes  |nike   |90   |175        |
|2       |2024/05/01|dress  |zara   |50   |65         |
|3       |2024/06/01|shoes  |nike   |85   |175        |
|4       |2025/01/01|t-shirt|zara   |15   |65         |
|5       |2025/04/01|bag    |parfois|25   |25         |
|6       |2025/05/01|bag    |carpisa|30   |30         |

Now, we’ve got added the identical aggregation as GROUP BY, while keeping all the person order details.

Syntax of Window Functions

Basically, the window function has a syntax that appears like this:

f(col2) OVER(
[PARTITION BY col1] 
[ORDER BY col3]
)

Let’s break it down. f(col2) is the operation you ought to perform, equivalent to sum, count and rating. OVER clause defines the “window” or the subset of rows over which the window function operates. PARTITION BY col1 divides the information into groups and ORDER BY col1 determines the order of rows inside each partition.

Furthermore, window functions fall into three essential categories:

• aggregate function:COUNT, SUM, AVG, MINand MAX
• rank function: ROW_NUMBER, RANK, DENSE_RANK, CUME_DIST, PERCENT_RANKandNTILE
• value function: LEAD, LAG, FIRST_VALUE and LAST_VALUE

4 Easy Examples

Let’s show different examples to master window functions.

Example 1: Easy Window Function

To grasp the concept of window functions, let’s start with an easy example. Suppose we would like to calculate the whole price of all of the orders within the table. Using a GROUP BY clause would give us a single value: 295. Nevertheless, that might collapse the rows and lose the person order details. As an alternative, if we would like to display the whole price alongside each record, we are able to use a window function like this:

SELECT 
    order_id,
    date,
    product,
    brand,
    price,
    SUM(price) OVER () as tot_price
FROM Orders

That is the output:

|order_id|date      |product|brand  |price|tot_price|
|--------|----------|-------|-------|-----|---------|
|1       |2024-02-01|shoes  |nike   |90   |295      |
|2       |2024-05-01|dress  |zara   |50   |295      |
|3       |2024-06-01|shoes  |nike   |85   |295      |
|4       |2025-01-01|t-shirt|zara   |15   |295      |
|5       |2025-04-01|bag    |parfois|25   |295      |
|6       |2025-05-01|bag    |carpisa|30   |295      |

In this manner, we obtained the sum of all prices over all the dataset and repeated it for every row.

Example 2: Partition by clause

Let’s now calculate the common price per 12 months while still keeping all the small print. We will do that by utilizing the PARTITION BY clause inside a window function to group rows by 12 months and compute the common inside each group:

SELECT 
    order_id,
    date,
    product,
    brand,
    price,
    round(AVG(price) OVER (PARTITION BY YEAR(date) as avg_price
FROM Orders

Here’s what the output looks like:

|order_id|date      |product|brand  |price|avg_price|
|--------|----------|-------|-------|-----|---------|
|1       |2024-02-01|shoes  |nike   |90   |75       |
|2       |2024-05-01|dress  |zara   |50   |75       |
|3       |2024-06-01|shoes  |nike   |85   |75       |
|4       |2025-01-01|t-shirt|zara   |15   |23.33    |
|5       |2025-04-01|bag    |parfois|25   |23.33    |
|6       |2025-05-01|bag    |carpisa|30   |23.33    |

That’s great! We see the common price for every year alongside each row.

Example 3: Order by clause

Top-of-the-line ways to know how ordering works inside window functions is to use a rating function. Let’s say we would like to rank all orders from highest to lowest price. Here’s how we are able to do it using the RANK() function:

SELECT 
    order_id,
    date,
    product,
    brand,
    price,
    RANK() OVER (ORDER BY price DESC) as Rank
FROM Orders

We obtain an output like this:

|order_id|date      |product|brand  |price|Rank|
|--------|----------|-------|-------|-----|----|
|1       |2024-02-01|shoes  |nike   |90   |1   |
|3       |2024-06-01|shoes  |nike   |85   |2   |
|2       |2024-05-01|dress  |zara   |50   |3   |
|6       |2025-05-01|bag    |carpisa|30   |4   |
|5       |2025-04-01|bag    |parfois|25   |5   |
|4       |2025-01-01|t-shirt|zara   |15   |6   |

As shown, the order with the very best price gets rank 1, and the remainder follow in descending order.

Example 4: Mix Partition by and Group by clauses

Within the previous example, we ranked all orders from the very best to the bottom price across all the dataset. But what if we would like to restart the rating for every year? We will do that by adding the PARTITION BY clause within the window function. This enables for splitting the information into separate groups by 12 months and sorting the orders from the very best to the bottom price.

SELECT 
    order_id,
    date,
    product,
    brand,
    price,
    RANK() OVER (PARTITION BY YEAR(date) ORDER BY price DESC) as Rank
FROM Orders

The result should appear like this:

|order_id|date      |product|brand  |price|Rank|
|--------|----------|-------|-------|-----|----|
|1       |2024-02-01|shoes  |nike   |90   |1   |
|3       |2024-06-01|shoes  |nike   |85   |2   |
|2       |2024-05-01|dress  |zara   |50   |3   |
|6       |2025-05-01|bag    |carpisa|30   |1   |
|5       |2025-04-01|bag    |parfois|25   |2   |
|4       |2025-01-01|t-shirt|zara   |15   |3   |

Now, the rating restarts for every year, as we decided. 

Final thoughts:

I hope this guide helped you get a transparent and practical introduction to SQL window functions. At first, they could feel a bit unintuitive, but when you compare them side by side with the GROUP BY clause, the worth they convey becomes much easier to know.

From my very own experience, window functions have been incredibly powerful for extracting insights without losing row-level detail, something that traditional aggregations hide. They’re incredibly useful when extracting metrics like totals, rankings, year-over-year or month-over-month comparisons.

Nevertheless, there are some limitations. Window functions might be computationally expensive, especially over large datasets or complex partitions. It’s essential to guage whether the added flexibility justifies the performance tradeoff in your specific use case.

Thanks for reading! Have a pleasant day!

Useful resources: