NTILE and PERCENT_RANK in Practice

They answer different questions about a distribution. NTILE divides the rows into equal-sized groups and labels them; PERCENT_RANK says where each row sits in the ordering as a fraction.

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, 'Grace Hopper',   12500.00),
  (2, 'Ada Lovelace',    5400.00),
  (3, 'Joan Clarke',     5100.00),
  (4, 'Alan Turing',      480.00),
  (5, 'Claude Shannon',   300.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 pct_rank
FROM customers
ORDER BY lifetime_spend DESC;

Five rows come back. The quartiles are 1, 2, 3, 4 and 4, and the percent ranks are 0, 0.25, 0.5, 0.75 and 1.0.

NTILE(n) splits the rows into n buckets of as equal a size as possible and labels them 1 to n. With five rows and four buckets the first three buckets get one row each and the last gets two, because the remainder is spread over the leading buckets. It is a rank-based split, not a value-based one — a bucket can contain values that are wildly different if the distribution is skewed.

PERCENT_RANK is different. It computes (rank minus one) divided by (number of rows minus one), so the top row is 0, the bottom row is 1, and the values in between are evenly spaced. The closest relative neighbour is CUME_DIST, which is (rank) divided by (number of rows) and therefore starts at 1 divided by n rather than at 0.

The practical difference shows up in how you use them. PERCENT_RANK is the right tool for "the top 10 per cent of customers" — filter on pct_rank < 0.1 — because the fraction is a real proportion of the population. NTILE is the right tool for "divide customers into four spending bands", because it guarantees roughly a quarter of the customers land in each band.

Two patterns, and the choice depends on whether you want a fixed fraction of the population or fixed value thresholds.

The population pattern uses PERCENT_RANK or CUME_DIST to label each row with its position, then filters on that label. To find the top 10 per cent of customers by spend, filter on pct_rank < 0.1. To find the top 25 per cent, use pct_rank < 0.25, or equivalently cume_dist <= 0.25. Because the label is computed over the whole result set, the fraction is exact — you get ten per cent of the rows, not approximately ten per cent.

The band pattern uses NTILE to split the population into fixed groups and then labels each group with a description. NTILE(4) gives you four roughly equal quartiles, and a CASE expression turns the number into something a report can print: the top quartile as 'platinum', the second as 'gold', and so on. Because NTILE distributes the remainder over the leading buckets, the top bucket is never empty and the band boundaries land on real rows.

There is a third approach that gives value thresholds rather than population fractions, and it is worth knowing because it is what most BI tools do. You compute the percentile value in a subquery — find the spend at the 90th percentile — and then compare every row against that number. That produces a threshold you can quote in a report: "anything above 9400 is top decile". NTILE and PERCENT_RANK do not give you that directly; they label rows.

One warning. All three depend on the ordering being deterministic. If two rows tie on the value being ranked, PERCENT_RANK gives them the same rank and therefore the same percentage, and the fraction of rows above the threshold is no longer exactly what you asked for. Add a unique tiebreaker to the ORDER BY if the percentages matter.

NTILE splits the population into four buckets, and a CASE expression turns the bucket number into a label.

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, 'Grace Hopper',   12500.00),
  (2, 'Ada Lovelace',    5400.00),
  (3, 'Joan Clarke',     5100.00),
  (4, 'Alan Turing',      480.00),
  (5, 'Claude Shannon',   300.00);

SELECT name,
       lifetime_spend,
       CASE NTILE(4) OVER (ORDER BY lifetime_spend DESC)
         WHEN 1 THEN 'platinum'
         WHEN 2 THEN 'gold'
         WHEN 3 THEN 'silver'
         ELSE 'bronze'
       END AS band
FROM customers
ORDER BY lifetime_spend DESC;

Five rows come back: Grace Hopper is platinum, Ada Lovelace is gold, Joan Clarke is silver, and Alan Turing and Claude Shannon are both bronze. Five customers into four buckets means one bucket holds two, and it is the last one because NTILE spreads the remainder over the leading buckets.

Two things worth knowing. NTILE counts rows, not value ranges, so the bands are equal in size but not in value. If spend is heavily skewed — one customer spending a hundred times the next — the platinum band might contain a single customer and the bronze band might cover a wide spread of modest spenders. If you need value bands rather than population bands, you have to compute the threshold values and compare against them instead.

Also note that the CASE here wraps the window function directly. That works because the window function is an expression inside the CASE, not an alias being referenced. You cannot write CASE quartile WHEN 1 ... in the same SELECT list, because the alias does not exist yet — that needs a subquery or a CTE.