What do NTILE and PERCENT_RANK tell you?
They both tell you where a row sits in the whole set, and they answer slightly different questions.
NTILE(n) cuts the rows into n buckets of about the same size and tells you which bucket each row is in. So NTILE(4) puts the top quarter in bucket 1, the next quarter in bucket 2, and so on. That is the shape that you want for quartiles and for splitting customers into bands.
PERCENT_RANK gives each row a number between zero and one, which is where the row sits in the order as a fraction. The first row always gets zero. The last row gets one less than one divided by the number of rows.
CREATE TABLE customers (
id INTEGER PRIMARY KEY,
name VARCHAR(120) NOT NULL,
lifetime_spend DECIMAL(12, 2) NOT NULL
);
INSERT INTO customers (id, name, lifetime_spend) VALUES
(1, 'Ada', 1000.00),
(2, 'Grace', 800.00),
(3, 'Alan', 600.00),
(4, 'Katherine', 400.00);
SELECT
name,
lifetime_spend,
NTILE(4) OVER (ORDER BY lifetime_spend DESC) AS quartile,
ROUND(PERCENT_RANK() OVER (ORDER BY lifetime_spend DESC), 3) AS percent_rank
FROM customers
ORDER BY lifetime_spend DESC;
name lifetime_spend quartile percent_rank
Ada 1000.00 1 0.0
Grace 800.00 1 0.25
Alan 600.00 2 0.5
Katherine 400.00 2 0.75
So what is the difference between them? NTILE tells you which band a row is in, and every band has roughly the same number of rows. PERCENT_RANK tells you exactly where the row sits as a fraction, so two rows can have different percentages even when they are in the same band.
There is a third one that is worth knowing, and it is called CUME_DIST. It gives the fraction of the rows that are at or below the current row, so it is always at least as big as the percent rank, and it tells you the share of the customers that you are beating.
The next two questions in this topic use them.