Categories
SQL 301

Why You Need SQL Window Functions (pt 2)

SQL Server Window Functions are incredibly powerful and allow us to access a total from another row and bucket data to show top performers.

Welcome back to the world of Window Functions.

Window Functions are closely related to Aggregate Functions. But rather than collapsing all the rows we want into one row, we keep them and add a new column with a running total, rank or moving average. This becomes our ‘window frame.’

It becomes clearer with examples and pictures, so if you’re interested in using this powerful tool, keep reading.


Introduction
Setting the Scene
Bucket rows into groups
Access a total from an earlier row
Use Window Functions to perform calculations


Introduction

SQL Server Window Functions come in three main types. These are:

Aggregate Window Functions
These use aggregate functions like SUM, COUNT, MAX, MIN over a set of rows and return a single result from the query.

Ranking Window Functions
These assign a ‘rank’ to a set of rows and using RANK, DENSE_RANK, ROW_NUMBER, NTILE

Value Window Functions
These use LAG, LEAD, FIRST_VALUE, LAST_VALUE to access a previous row without having to do a self join.


Setting the Scene

This post is picks up where we left off in Part One, where we used Window Functions to answer questions from a Sales Manager who is setting targets for the year.

In the last post we covered Aggregate Window Functions and how to use RANK(). In this post we’ll be exploring how to use NTILE(), then exploring how we can use Value Window Functions to answer questions using Sales data.


sql server window functions
The raw Sales Order table, not much use to your Sales Manager

Bucket rows into groups

When we left him in the last post we had provided the Sales Manager two tables showing daily sales with a running total, and all sales ranked by their dollar value.

This time the Sales Manager would like to give a bonus to the Territory that has bought in the top 25% of customers over the last year.

To do this we can use NTILE() to bucket the dollar values into four groups that represent four quartiles.



NTILE() can be used to bucket groups of rows and can be adjusted by changing the number in the brackets.

sql server window functions

Access a total from an earlier row

The Sales Manager needs one last thing before the targets for the next year can be locked in. A table that shows the ‘big customers’ identified earlier and the dates they are making orders.

What the Sales Manager needs for this last piece of work is an idea of how many days there are between orders for these big customers. If he can anticipate when they are going to buy next we can get in touch with them earlier.



By using LAG() we are able to reach up and return the result from the previous row and add it to a new column. LEAD() works in the same way but in reverse.

sql server window functions

Use Window Functions to perform calculations

To make this useful for the Sales Manager we can use CAST and a new column to calculate the difference between the dates.



sql server window functions

I hope you’ve enjoyed these two posts on SQL Window Functions. How do you use these in your work? Do you find them a useful and powerful tool?


Photo by Dids from Pexels

By Helen Anderson

I’m passionate about technology and building data applications.