What Does the NTILE Function Do?

Have you ever used the NTILE function? Or have you even heard of the NTILE function? It seems to be one of the lesser known, lesser used window functions in SQL Server. I’ve never come across it in the wild but maybe there are those that use it all the time. Either way, let’s have a look at what it does and how it can be used.

All Four Quarters

The NTILE function can be used to divide a set of results into equally sized groups. The group numbering starts at 1 and can be divided up from there.

For our example, imagine we’re studying comic book sales and we want to break our results down into four quartiles based on issue revenue. We may have this set of data to start with:

We can scan the data in this order and see that Revenue went up, dipped back down, and then came back up for the final issue.

With the NTILE function, let’s divide our data and order it by revenue in descending order:

SELECT Issue_Number,Sale_Date,Copies_Sold,Revenue,
	NTILE(4) OVER (ORDER BY Revenue DESC) AS Quartile
FROM ComicSales;
GO

Our data has been divided up and we can see the quartile values in the Quartile column. Using NTILE, we can see that issues 3 and 8 were the top quartile, issues 2 and 4 were next, and so on. Maybe issues 3 and 8 were conclusions to a particular story arc and that’s why they were more sought after than the others?

Divvy it up

What if we are dividing our data into 3 groups (tertiles) instead of 4 (quartiles)? Will this still work with 8 issues?

SELECT Issue_Number,Sale_Date,Copies_Sold,Revenue,
	NTILE(3) OVER (ORDER BY Revenue DESC) AS Tertile
FROM ComicSales;
GO

The query completes successfully and our Tertile column has the first three issues a value of 1, the second three with a value of 2, and the remaing two issues a value of 3.

Try it out

We looked a pretty basic example here. Hopefully you read this and see some ways NTILE can be applied as you analyze your own data.

Thanks for reading!