NTILE()

Syntax

How it works

NTILE(n) takes the rows in a window, puts them in the order you specify, and splits them into n contiguous buckets of as-equal-as-possible size. Each row is stamped with the number of the bucket it landed in, from 1 for the first bucket up to n for the last. It is the standard tool for building quartiles, deciles and other equal-size groupings.

The most common uses are NTILE(4) for quartiles, NTILE(10) for deciles, and NTILE(100) for percentiles, but any value works: NTILE(3) splits a customer list into three tiers. Unlike RANK(), which reacts to ties in the value, NTILE only cares about position, so it fills buckets purely by row order.

When the row count does not divide evenly by n, NTILE puts the extra rows in the earlier buckets. If 10 rows are split into 3 buckets, the sizes are 4, 3 and 3, not 3, 3, 4. This is defined behaviour, not a bug, and it is the same across every engine. Like all window functions, NTILE requires an OVER() clause and needs MySQL 8.0+ or SQLite 3.25+; PostgreSQL and SQL Server have supported it for years.

Examples

Quartiles of order value

SELECT id, amount,
       NTILE(4) OVER (ORDER BY amount) AS quartile
FROM orders;

id | amount | quartile
---+--------+---------
 5 |     12 |        1
 3 |     40 |        1
 8 |     75 |        2
 1 |     90 |        3
 7 |    150 |        4

Deciles of test scores

-- split students into 10 equal bands, band 1 = lowest
SELECT name, score,
       NTILE(10) OVER (ORDER BY score DESC) AS decile
FROM results;

name  | score | decile
------+-------+-------
 Sara |    99 |      1
 Omar |    95 |      1
 Lina |    88 |      2
 Ali  |    71 |      5
 Noor |    44 |     10

Split customers into 3 tiers

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

name  | total_spent | tier
------+-------------+-------
 Omar |        9800 | gold
 Sara |        6100 | gold
 Lina |        4200 | silver
 Ali  |        3000 | silver
 Noor |         900 | bronze

Bucket within each group with PARTITION BY

-- quartiles computed separately per country
SELECT country, name, revenue,
       NTILE(4) OVER (PARTITION BY country
                      ORDER BY revenue DESC) AS q
FROM sales;

country | name | revenue | q
--------+------+---------+---
 UAE    | A    |    5000 | 1
 UAE    | B    |    3000 | 2
 UAE    | C    |    1000 | 3
 KSA    | D    |    8000 | 1
 KSA    | E    |    2000 | 2

Uneven split shows extra rows in early buckets

-- 5 rows into 2 buckets -> sizes 3 and 2
SELECT n,
       NTILE(2) OVER (ORDER BY n) AS bucket
FROM (SELECT 1 AS n UNION SELECT 2 UNION SELECT 3
      UNION SELECT 4 UNION SELECT 5) t;

n | bucket
--+-------
1 |      1
2 |      1
3 |      1
4 |      2
5 |      2

Common mistakes

-- Expecting perfectly equal buckets
-- 10 rows / 3 does NOT give 3, 3, 4
SELECT id,
       NTILE(3) OVER (ORDER BY id) AS bucket
FROM ten_rows;  -- sizes are 4, 3, 3

-- This is correct behaviour: earlier buckets get
-- the extra rows first. Do not fight it.
SELECT id,
       NTILE(3) OVER (ORDER BY id) AS bucket
FROM ten_rows;  -- buckets: 4 rows, 3 rows, 3 rows

-- Missing ORDER BY: the result is meaningless
SELECT id,
       NTILE(4) OVER () AS quartile
FROM orders;

SELECT id,
       NTILE(4) OVER (ORDER BY amount) AS quartile
FROM orders;

-- Treating the bucket number as a real percentile value
SELECT amount,
       NTILE(100) OVER (ORDER BY amount) AS pct
FROM orders;  -- pct is a bucket label, not a cutoff

-- For an actual percentile value use a percentile function
SELECT PERCENTILE_CONT(0.9)
         WITHIN GROUP (ORDER BY amount) AS p90
FROM orders;

Performance

NTILE() requires the engine to order the rows in the window before it can assign buckets, so its cost is dominated by that sort. If an index already provides the rows in the ORDER BY order, the optimiser can skip the sort and the function becomes very cheap.

With PARTITION BY the sort happens once per partition, which is usually cheaper than one giant sort. Keeping the partition and order columns covered by an index lets the database stream rows straight into the buckets instead of spilling a large sort to disk.

NTILE only needs to know the total row count per partition and each row position, so it does not carry the heavy state of a running aggregate. On large result sets the practical limit is memory for the sort; see the indexing guide for how ordered scans avoid it.

Interview questions

SELECT name, total_spent,
       NTILE(4) OVER (ORDER BY total_spent DESC) AS quartile
FROM customers;