Snowflake SQL Tutorial 51 - NTILE, PERCENT_RANK and CUME_DIST in Snowflake

Snowflake SQL · Episode 51

In this post we are going to cover NTILE, PERCENT_RANK and CUME_DIST in Snowflake.

All three of them answer the same question. Where does this row sit among the other rows? They just answer it in different ways. NTILE gives us a bucket number. PERCENT_RANK and CUME_DIST each give us a fraction between 0 and 1.

We will take them one at a time, and then put all of them in one query so we can read them side by side. On the way we will find the one thing NTILE does that the other two do not. It will split two rows that hold the same value.

Every query below was run on a real Snowflake account, and every result you see is the output it gave back.

Episode 51 of the Snowflake SQL: Zero to Hero series on TechBrothersIT.

On this page

1. Let's create some sample data
2. Let's start with NTILE and split the rows into buckets
3. Now let's ask for three buckets instead of four
4. Now let's see what NTILE does with our tie
5. Now let's try PERCENT_RANK
6. Now let's look at CUME_DIST
7. Let's put the two fractions side by side
8. Can we have all four in one query?
9. Let's try quartiles on a bigger table

Let's create some sample data

Before we can put rows into buckets we need some rows. The script below creates the EMPLOYEES table and puts eight rows into it.

Copy it into a Snowflake worksheet and run it once. Every post in this series uses the same table, so you only have to do this one time.

Sample data

-- EMPLOYEES comes from Episode 1 and ORDERS from Level 3. Every episode in
-- this series reuses the same tables, so you only set them up once.
USE DATABASE DEMO_DB;
USE SCHEMA PUBLIC;

CREATE OR REPLACE TABLE EMPLOYEES (
    EMP_ID          INT,
    FIRST_NAME      VARCHAR,
    LAST_NAME       VARCHAR,
    EMAIL           VARCHAR,
    DEPT_ID         INT,
    MANAGER_ID      INT,
    SALARY          NUMBER(10,2),
    COMMISSION_PCT  NUMBER(4,2),
    HIRE_DATE       DATE
);

-- Marcus and Priya are on the same SALARY. That one duplicated value is what
-- separates the three functions on this page.
INSERT INTO EMPLOYEES VALUES
    (1, 'Aisha',  'Khan',      'aisha.khan@example.com',      10,    6, 118000.00, NULL,  '2019-03-11'),
    (2, 'Marcus', 'Reid',      'marcus.reid@example.com',     10,    6,  96500.00, NULL,  '2021-07-01'),
    (3, 'Lena',   'Fischer',   'lena.fischer@example.com',    20,    6, 104000.00, 0.05,  '2018-01-22'),
    (4, 'Tomas',  'Novak',     'tomas.novak@example.com',     20,    3,  87250.00, NULL,  '2022-09-05'),
    (5, 'Priya',  'Raman',     'priya.raman@example.com',     30,    6,  96500.00, 0.10,  '2020-11-17'),
    (6, 'Daniel', 'O''Connor', 'daniel.oconnor@example.com',  10, NULL, 131000.00, NULL,  '2016-05-30'),
    (7, 'Sofia',  'Marino',    'sofia.marino@example.com',    30,    5,  71400.00, 0.08,  '2023-02-13'),
    (8, 'Grace',  'Lin',       'grace.lin@example.com',     NULL,    6,  92000.00, NULL,  '2021-04-19');

Two things about this data matter for this post. There are eight rows, which is small enough that we can check every bucket by eye.

And two people, Marcus and Priya, are on exactly the same salary. That is the tie Episode 50 used to separate RANK from DENSE_RANK, and we are going to come back to it on every grid on this page.

Let's start with NTILE and split the rows into buckets

NTILE takes a number and splits the window into that many buckets. The buckets are numbered from 1.

Let's ask for four buckets over ORDER BY SALARY DESC. That gives us quartiles, highest paid first.

Query

SELECT FIRST_NAME, SALARY,
       NTILE(4) OVER (ORDER BY SALARY DESC) AS QUARTILE
FROM EMPLOYEES
ORDER BY SALARY DESC, FIRST_NAME;

Result8 rows · 3 columns

FIRST_NAME	SALARY	QUARTILE
Daniel	131000.00	1
Aisha	118000.00	1
Lena	104000.00	2
Marcus	96500.00	2
Priya	96500.00	3
Grace	92000.00	3
Tomas	87250.00	4
Sofia	71400.00	4

Read the QUARTILE column down. The two highest-paid people are in quartile 1, the next two are in 2, and so on down to 4. Eight rows, four buckets, two rows in each.

That is easy to say, so let's count it instead of trusting it. We wrap the same window function in a subquery and GROUP BY the bucket.

Query — how big is each bucket?

SELECT QUARTILE, COUNT(*) AS N
FROM (SELECT NTILE(4) OVER (ORDER BY SALARY DESC) AS QUARTILE FROM EMPLOYEES)
GROUP BY QUARTILE ORDER BY QUARTILE;

Result4 rows · 2 columns

QUARTILE	N
1	2
2	2
3	2
4	2

Four rows, and every N is the same. It divided evenly because the row count divides by the number of buckets.

What it needs

NTILE needs an ORDER BY inside OVER. Without one there is no sequence to cut into buckets, so there is nothing for the function to mean. It takes a PARTITION BY like every other window function, and then numbers the buckets afresh inside each partition.

Now let's ask for three buckets instead of four

Most real tables do not divide neatly by the number of buckets we want. Eight rows into three buckets cannot come out even, so something has to give.

Let's run it and see which buckets take the extra rows.

Query

SELECT FIRST_NAME, SALARY,
       NTILE(3) OVER (ORDER BY SALARY DESC) AS THIRD
FROM EMPLOYEES
ORDER BY SALARY DESC, FIRST_NAME;

Result8 rows · 3 columns

FIRST_NAME	SALARY	THIRD
Daniel	131000.00	1
Aisha	118000.00	1
Lena	104000.00	1
Marcus	96500.00	2
Priya	96500.00	2
Grace	92000.00	2
Tomas	87250.00	3
Sofia	71400.00	3

Query — and the sizes, counted

SELECT THIRD, COUNT(*) AS N
FROM (SELECT NTILE(3) OVER (ORDER BY SALARY DESC) AS THIRD FROM EMPLOYEES)
GROUP BY THIRD ORDER BY THIRD;

Result3 rows · 2 columns

THIRD	N
1	3
2	3
3	2

The earlier buckets take the extra rows. Bucket 1 and bucket 2 got three rows each. Bucket 3 got two.

NTILE never makes two buckets differ in size by more than one row. When there is a remainder it hands the spare rows to the lowest-numbered buckets.

Hold on to that rule. We will see it again when we put quartiles on the bigger ORDERS table, where the arithmetic is less obvious and the behaviour is the same.

Now let's see what NTILE does with our tie

Go back to the quartile grid and find Marcus and Priya, the two people on the same salary. Marcus is in quartile 2. Priya is in quartile 3. Identical value, different bucket.

This is the one that bites

NTILE is not a ranking function and it makes no promise about equal values. Its contract is about bucket size, and it will split a tied pair down the middle to keep the sizes right. If two rows with the same value have to land in the same group, NTILE is the wrong function. PERCENT_RANK and CUME_DIST keep tied rows together, and we will see that shortly.

It is worth being concrete about where this hurts. Build a quartile column with NTILE over a column that has a lot of repeated values, like a score or a rating or a round number of units, and identical rows will land in different quartiles. Which of them lands where depends on how the ORDER BY broke the tie. Add a second, unique ORDER BY key if you need that to be stable.

And what if we ask for more buckets than we have rows?

This one is harmless, but it is worth seeing once so it never puzzles you. Let's ask for twenty buckets over our eight rows.

Query

SELECT FIRST_NAME, SALARY,
       NTILE(20) OVER (ORDER BY SALARY DESC) AS BUCKET
FROM EMPLOYEES
ORDER BY SALARY DESC, FIRST_NAME;

Result8 rows · 3 columns

FIRST_NAME	SALARY	BUCKET
Daniel	131000.00	1
Aisha	118000.00	2
Lena	104000.00	3
Marcus	96500.00	4
Priya	96500.00	5
Grace	92000.00	6
Tomas	87250.00	7
Sofia	71400.00	8

Every row got a bucket of its own, numbered from 1 upwards until the rows ran out. The higher-numbered buckets simply never appear. Nothing errored, nothing came back empty, and no row carries a NULL bucket.

So when the bucket count is at or above the row count, NTILE is doing the same job as ROW_NUMBER.

Now let's try PERCENT_RANK

PERCENT_RANK is built directly out of RANK, which we covered in Episode 50. So the easiest way to read it is with RANK sitting beside it.

Query

SELECT FIRST_NAME, SALARY,
       RANK()         OVER (ORDER BY SALARY DESC) AS RNK,
       PERCENT_RANK() OVER (ORDER BY SALARY DESC) AS PCT_RANK
FROM EMPLOYEES
ORDER BY SALARY DESC, FIRST_NAME;

Result8 rows · 4 columns

FIRST_NAME	SALARY	RNK	PCT_RANK
Daniel	131000.00	1	0.0
Aisha	118000.00	2	0.14285714285714285
Lena	104000.00	3	0.2857142857142857
Marcus	96500.00	4	0.42857142857142855
Priya	96500.00	4	0.42857142857142855
Grace	92000.00	6	0.7142857142857143
Tomas	87250.00	7	0.8571428571428571
Sofia	71400.00	8	1.0

The top row is 0 and the bottom row is 1. Everything else is spread between them.

The first row is always exactly zero. The measure is how far down the ranking we are, and the top row is no distance down it at all.

Now look at Marcus and Priya again. They share a RNK, and they share a PCT_RANK as well. Ties stay together, which is the opposite of what NTILE did to that pair.

That falls straight out of the formula, because the formula is built on RANK and RANK keeps them together:

PERCENT_RANK = (RANK − 1) / (number of rows − 1)

That is short enough to work out ourselves. Let's compute it by hand next to the built-in function and compare the two columns.

Query — the formula, written out beside the built-in

SELECT FIRST_NAME, SALARY,
       RANK() OVER (ORDER BY SALARY DESC) AS RNK,
       COUNT(*) OVER () AS N_ROWS,
       (RANK() OVER (ORDER BY SALARY DESC) - 1) / (COUNT(*) OVER () - 1) AS BY_HAND,
       PERCENT_RANK() OVER (ORDER BY SALARY DESC) AS BUILT_IN
FROM EMPLOYEES
ORDER BY SALARY DESC, FIRST_NAME;

Result8 rows · 6 columns · scroll →

FIRST_NAME	SALARY	RNK	N_ROWS	BY_HAND	BUILT_IN
Daniel	131000.00	1	8	0.000000	0.0
Aisha	118000.00	2	8	0.142857	0.14285714285714285
Lena	104000.00	3	8	0.285714	0.2857142857142857
Marcus	96500.00	4	8	0.428571	0.42857142857142855
Priya	96500.00	4	8	0.428571	0.42857142857142855
Grace	92000.00	6	8	0.714286	0.7142857142857143
Tomas	87250.00	7	8	0.857143	0.8571428571428571
Sofia	71400.00	8	8	1.000000	1.0

BY_HAND and BUILT_IN carry the same number on every row. They do not print the same, and that is worth a sentence rather than a shrug. Dividing two numeric values gives a fixed-point result, rounded to the scale Snowflake assigns the division. PERCENT_RANK returns a float and prints every digit it has. Compare them digit by digit and the hand-built column agrees as far as it goes.

The denominator

Notice the denominator: rows − 1, not rows. N_ROWS in that same grid is the row count, and the divisor is one less than it. That is exactly why the bottom row reaches 1 instead of stopping short of it.

Now let's look at CUME_DIST

CUME_DIST is short for cumulative distribution. It answers a question that sounds like the PERCENT_RANK one and is not. Not how far down the ranking am I, but what fraction of the window is at my value or ahead of it, counting me.

Query

SELECT FIRST_NAME, SALARY,
       CUME_DIST() OVER (ORDER BY SALARY DESC) AS CUME
FROM EMPLOYEES
ORDER BY SALARY DESC, FIRST_NAME;

Result8 rows · 3 columns

FIRST_NAME	SALARY	CUME
Daniel	131000.00	0.125
Aisha	118000.00	0.25
Lena	104000.00	0.375
Marcus	96500.00	0.625
Priya	96500.00	0.625
Grace	92000.00	0.75
Tomas	87250.00	0.875
Sofia	71400.00	1.0

The top row is not zero. It is one row out of eight, because CUME_DIST counts the current row as being at its own value. So 0 is a value this function can never return, and that single fact is the whole difference from PERCENT_RANK.

Our tied pair is where the counting gets interesting. Both of them carry the same figure. Count the rows at 96500.00 or above in that grid and we get five: Daniel, Aisha, Lena, Marcus and Priya. Both halves of the tie are counted. Five rows out of eight.

So we can work this one out by hand too, from a running count over a total count.

Query — the formula, written out beside the built-in

SELECT FIRST_NAME, SALARY,
       COUNT(*) OVER (ORDER BY SALARY DESC) AS ROWS_AT_OR_BEFORE,
       COUNT(*) OVER ()                     AS N_ROWS,
       COUNT(*) OVER (ORDER BY SALARY DESC) / COUNT(*) OVER () AS BY_HAND,
       CUME_DIST() OVER (ORDER BY SALARY DESC) AS BUILT_IN
FROM EMPLOYEES
ORDER BY SALARY DESC, FIRST_NAME;

Result8 rows · 6 columns · scroll →

FIRST_NAME	SALARY	ROWS_AT_OR_BEFORE	N_ROWS	BY_HAND	BUILT_IN
Daniel	131000.00	1	8	0.125000	0.125
Aisha	118000.00	2	8	0.250000	0.25
Lena	104000.00	3	8	0.375000	0.375
Marcus	96500.00	5	8	0.625000	0.625
Priya	96500.00	5	8	0.625000	0.625
Grace	92000.00	6	8	0.750000	0.75
Tomas	87250.00	7	8	0.875000	0.875
Sofia	71400.00	8	8	1.000000	1.0

ROWS_AT_OR_BEFORE is a running COUNT(*) over the same ORDER BY. That is the default window frame from Episode 48 doing its job: everything from the start of the window up to and including the current row’s peers. Read that column on the tied rows. Both of them say the same thing, because RANGE-based framing includes every row with an equal value. Divide by N_ROWS and we have CUME_DIST.

Why these two columns match digit for digit

Here the hand-built column and the built-in print identically apart from trailing zeros, because eighths are exactly representable. Do not read that as a general rule. The hand-built PERCENT_RANK column was the same arithmetic over sevenths, and sevenths are not.

Let's put the two fractions side by side

One grid makes the contrast permanent. Same window specification, both functions.

Query

SELECT FIRST_NAME, SALARY,
       PERCENT_RANK() OVER (ORDER BY SALARY DESC) AS PCT_RANK,
       CUME_DIST()    OVER (ORDER BY SALARY DESC) AS CUME
FROM EMPLOYEES
ORDER BY SALARY DESC, FIRST_NAME;

Result8 rows · 4 columns

FIRST_NAME	SALARY	PCT_RANK	CUME
Daniel	131000.00	0.0	0.125
Aisha	118000.00	0.14285714285714285	0.25
Lena	104000.00	0.2857142857142857	0.375
Marcus	96500.00	0.42857142857142855	0.625
Priya	96500.00	0.42857142857142855	0.625
Grace	92000.00	0.7142857142857143	0.75
Tomas	87250.00	0.8571428571428571	0.875
Sofia	71400.00	1.0	1.0

	PERCENT_RANK	CUME_DIST
Question it answers	How far down the ranking am I?	What fraction is at my value or better?
First row	always 0	1 ÷ number of rows, never 0
Last row	1	1
Numerator	RANK − 1	rows at this value or ahead of it
Denominator	rows − 1	rows
Tied rows	share a value	share a value

Both of them finish at 1. Both of them keep ties together. They differ at the top of the window, and in what they are counting. The one thing to remember is the first row: PERCENT_RANK starts at zero, CUME_DIST cannot.

Which one to use is a question about what you were asked for. “This student scored better than this share of the cohort” is a percent-rank statement. It is about the rows strictly below. “This order is in the top slice of orders by value” is a cume-dist statement. It is about the rows at this value or above, including this one. People use the two phrases interchangeably, and the arithmetic is far enough apart that it shows up in a report.

Can we have all four in one query?

We can. Here is NTILE, RANK, PERCENT_RANK and CUME_DIST over one window specification. This is the grid worth bookmarking out of the whole post.

Query

SELECT FIRST_NAME, SALARY,
       NTILE(4)       OVER (ORDER BY SALARY DESC) AS QUARTILE,
       RANK()         OVER (ORDER BY SALARY DESC) AS RNK,
       PERCENT_RANK() OVER (ORDER BY SALARY DESC) AS PCT_RANK,
       CUME_DIST()    OVER (ORDER BY SALARY DESC) AS CUME
FROM EMPLOYEES
ORDER BY SALARY DESC, FIRST_NAME;

Result8 rows · 6 columns · scroll →

FIRST_NAME	SALARY	QUARTILE	RNK	PCT_RANK	CUME
Daniel	131000.00	1	1	0.0	0.125
Aisha	118000.00	1	2	0.14285714285714285	0.25
Lena	104000.00	2	3	0.2857142857142857	0.375
Marcus	96500.00	2	4	0.42857142857142855	0.625
Priya	96500.00	3	4	0.42857142857142855	0.625
Grace	92000.00	3	6	0.7142857142857143	0.75
Tomas	87250.00	4	7	0.8571428571428571	0.875
Sofia	71400.00	4	8	1.0	1.0

Four different currencies for the same idea. RANK gives us a position, NTILE gives us a bucket, and the other two give us a fraction.

Read across our tied pair one more time, because this is the grid where the whole lesson is visible in one place. RNK, PCT_RANK and CUME all give those two rows the same value. QUARTILE is the one column that does not.

Let's try quartiles on a bigger table

EMPLOYEES is small enough to check by eye, which is why we used it. The thing we would actually do with NTILE needs a table with some size to it.

So let's do it on ORDERS. We cut the orders into quartiles by amount, then group by the quartile and ask for the size and the range of each one.

Query

SELECT QUARTILE, COUNT(*) AS N, MIN(AMOUNT) AS LOWEST, MAX(AMOUNT) AS HIGHEST
FROM (SELECT AMOUNT, NTILE(4) OVER (ORDER BY AMOUNT DESC) AS QUARTILE FROM ORDERS)
GROUP BY QUARTILE ORDER BY QUARTILE;

Result4 rows · 4 columns

QUARTILE	N	LOWEST	HIGHEST
1	11	7890.00	13100.00
2	10	5020.00	7460.00
3	10	1975.50	4760.00
4	10	640.00	1690.00

Four rows, and a complete summary of the order book’s shape. Each row tells us how many orders fell in that quarter, and the lowest and highest amount inside it. So the LOWEST of one quartile and the HIGHEST of the next are the cut points.

Look at the bucket sizes. That is the same rule we saw with three buckets over eight rows, on a bigger table. Add them up: 11 + 10 + 10 + 10 = 41. That should be every order in the table, and we can check it rather than assume it.

Query — the check

SELECT COUNT(*) AS N_ORDERS FROM ORDERS;

Result1 row · 1 column

N_ORDERS
41

41 orders, 41 orders bucketed, and the remainder of one went to the first bucket. NTILE assigns every row in the window to exactly one bucket. There is no overflow and nothing is dropped.

Why the subquery is there

This is the shape of a lot of useful analysis: NTILE in a subquery, then GROUP BY the bucket on the outside. You cannot aggregate a window function in the same SELECT that computes it, for the same reason you cannot filter one in the same WHERE. Window functions are evaluated after GROUP BY has already happened.

Key points

• NTILE(n) splits the window into n buckets of as near as possible equal size, numbered from 1, and requires an ORDER BY inside OVER.
• When the rows do not divide evenly, the earlier buckets take the extra rows, and no two buckets ever differ in size by more than one row.
• NTILE will split a tie. Two rows with the same value can land in different buckets, because the function’s contract is about bucket size, not about equal values.
• Ask for more buckets than you have rows and you get one row per bucket until the rows run out. The remaining bucket numbers never appear, and nothing errors.
• PERCENT_RANK is (RANK − 1) ÷ (rows − 1). The top row is always 0, the bottom row is 1, and tied rows share a value.
• CUME_DIST is the number of rows at this value or ahead of it, divided by the row count. It counts the current row, so it starts at one over the row count and can never be 0.
• Both fractions end at 1 and both keep tied rows together. The difference that matters in practice is at the top of the window.
• Dividing numbers by hand gives a fixed-point result while these functions return floats, so a hand-built column can be the same number and still print differently.
• The practical pattern is NTILE in a subquery and GROUP BY the bucket outside it. Bucket sizes and ranges in four rows.

Next in this series

Episode 52 — LAG and LEAD. From where does this row sit to what did the row before it say: reaching backwards and forwards for a value, month-on-month change in one line, and what a default of zero quietly does to it.

Every query and every result grid on this page was captured from a live Snowflake account (version 10.34.101) running the demo script for this episode. The EMPLOYEES and ORDERS data is invented demo data created by that script. No performance or benchmark claims are made anywhere in this series. Function behaviour follows the NTILE documentation, the PERCENT_RANK documentation and the CUME_DIST documentation.