Mastering SQL Window Functions | Towards Data Science

in my work, I’ve written numerous SQL queries to extract insights from knowledge. It’s all the time a difficult activity as a result of it’s not solely necessary to write down environment friendly queries, but additionally easy sufficient to keep up over time.

With every new downside comes a brand new lesson, and lately, I’ve been diving into SQL window capabilities. These highly effective instruments are extremely helpful when you could carry out calculations throughout a set of rows with out shedding the granularity of particular person data. 

On this article, I’ll break down SQL window capabilities step-by-step. They may appear complicated or unintuitive at first, however when you perceive how they work, you’ll see how indispensable they are often. Are you prepared? Let’s dive in and grasp them collectively!

Desk of contents

• Why do we’d like Window Features?
• Syntax of Window Operate 
• 4 Easy Examples

Why do we’d like Window Features?

To grasp the ability of Window Features, let’s begin with a easy instance. Think about we’ve a desk containing six orders from an e-commerce web site. Every row contains the order id, the date, the product, its model and worth.

Let’s suppose that we wish to calculate the full worth for every model. Utilizing the GROUP BY clause, we are able to write a question like this:

SELECT 
      model, 
      SUM(worth) as total_price 
FROM Orders 
GROUP BY model

This returns a consequence the place every row represents one model, together with the full worth of all orders underneath that model. 

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

This aggregation removes the small print of particular person orders, for the reason that output solely contains one row for model. What if we wish to hold all the unique rows and add the full worth for every model as an additional area? 

By utilizing SUM(worth) OVER (PARTITION BY model), we are able to calculate the full worth for every model with out collapsing the rows:

SELECT 
    order_id,
    date,
    product,
    model,
    worth,
    SUM(worth) OVER (PARTITION BY model) as total_price
FROM Orders

We’ve obtained a consequence like this:

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

This question returns all six rows, preserving each particular person order, and provides a brand new column exhibiting the full worth per model. For instance, the order with model Carpisa reveals a complete of 30, because it’s the one Carpisa order, the 2 orders from Nike present 175 (90+85), and so forth. 

You could discover that the desk is not ordered by order_id. That’s as a result of the window perform partitions by model, and SQL doesn’t assure row order except explicitly specified. To revive the unique order, we have to merely add an ORDER BY clause:

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

Lastly, we’ve the output containing all of the required particulars:

|order_id|date      |product|model  |worth|total_price|
|--------|----------|-------|-------|-----|-----------|
|1       |2024/02/01|footwear  |nike   |90   |175        |
|2       |2024/05/01|costume  |zara   |50   |65         |
|3       |2024/06/01|footwear  |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 added the identical aggregation as GROUP BY, whereas maintaining all the person order particulars.

Syntax of Window Features

Usually, the window perform 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 wish to carry out, similar to sum, depend and rating. OVER clause defines the “window” or the subset of rows over which the window perform operates. PARTITION BY col1 divides the information into teams and ORDER BY col1 determines the order of rows inside every partition.

Furthermore, window capabilities fall into three most important classes:

• mixture perform:COUNT, SUM, AVG, MINand MAX
• rank perform: ROW_NUMBER, RANK, DENSE_RANK, CUME_DIST, PERCENT_RANKandNTILE
• worth perform: LEAD, LAG, FIRST_VALUE and LAST_VALUE

4 Easy Examples

Let’s present completely different examples to grasp window capabilities.

Instance 1: Easy Window Operate

To grasp the idea of window capabilities, let’s begin with an easy instance. Suppose we wish to calculate the full worth of all of the orders within the desk. Utilizing a GROUP BY clause would give us a single worth: 295. Nevertheless, that might collapse the rows and lose the person order particulars. As an alternative, if we wish to show the full worth alongside every report, we are able to use a window perform like this:

SELECT 
    order_id,
    date,
    product,
    model,
    worth,
    SUM(worth) OVER () as tot_price
FROM Orders

That is the output:

|order_id|date      |product|model  |worth|tot_price|
|--------|----------|-------|-------|-----|---------|
|1       |2024-02-01|footwear  |nike   |90   |295      |
|2       |2024-05-01|costume  |zara   |50   |295      |
|3       |2024-06-01|footwear  |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      |

On this means, we obtained the sum of all costs over the complete dataset and repeated it for every row.

Instance 2: Partition by clause

Let’s now calculate the typical worth per 12 months whereas nonetheless maintaining all the small print. We will do that by utilizing the PARTITION BY clause inside a window perform to group rows by 12 months and compute the typical inside every group:

SELECT 
    order_id,
    date,
    product,
    model,
    worth,
    spherical(AVG(worth) OVER (PARTITION BY YEAR(date) as avg_price
FROM Orders

Right here’s what the output seems to be like:

|order_id|date      |product|model  |worth|avg_price|
|--------|----------|-------|-------|-----|---------|
|1       |2024-02-01|footwear  |nike   |90   |75       |
|2       |2024-05-01|costume  |zara   |50   |75       |
|3       |2024-06-01|footwear  |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 nice! We see the typical worth for every year alongside every row.

Instance 3: Order by clause

Top-of-the-line methods to grasp how ordering works inside window capabilities is to use a rating perform. Let’s say we wish to rank all orders from highest to lowest worth. Right here’s how we are able to do it utilizing the RANK() perform:

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

We acquire an output like this:

|order_id|date      |product|model  |worth|Rank|
|--------|----------|-------|-------|-----|----|
|1       |2024-02-01|footwear  |nike   |90   |1   |
|3       |2024-06-01|footwear  |nike   |85   |2   |
|2       |2024-05-01|costume  |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 proven, the order with the best worth will get rank 1, and the remaining comply with in descending order.

Instance 4: Mix Partition by and Group by clauses

Within the earlier instance, we ranked all orders from the best to the bottom worth throughout the complete dataset. However what if we wish to restart the rating for every year? We will do that by including the PARTITION BY clause within the window perform. This permits for splitting the information into separate teams by 12 months and sorting the orders from the best to the bottom worth.

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

The consequence ought to appear like this:

|order_id|date      |product|model  |worth|Rank|
|--------|----------|-------|-------|-----|----|
|1       |2024-02-01|footwear  |nike   |90   |1   |
|3       |2024-06-01|footwear  |nike   |85   |2   |
|2       |2024-05-01|costume  |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 determined. 

Ultimate ideas:

I hope this information helped you get a transparent and sensible introduction to SQL window capabilities. At first, they may really feel a bit unintuitive, however when you evaluate them facet by facet with the GROUP BY clause, the worth they bring about turns into a lot simpler to grasp.

From my very own expertise, window capabilities have been extremely highly effective for extracting insights with out shedding row-level element, one thing that conventional aggregations cover. They’re extremely helpful when extracting metrics like totals, rankings, year-over-year or month-over-month comparisons.

Nevertheless, there are some limitations. Window capabilities will be computationally costly, particularly over giant datasets or complicated partitions. It’s necessary to guage whether or not the added flexibility justifies the efficiency tradeoff in your particular use case.

Thanks for studying! Have a pleasant day!

Helpful sources: