NTILE(n) divides the ordered rows of a partition into n buckets of (nearly) equal size, and returns the bucket number (1 to n) for each row.
query.sql
SQL
CREATE TABLE employees (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(100) NOT NULL,
department VARCHAR(50) NOT NULL,
salary DECIMAL(10,2) NOT NULL,
manager_id INT NULL,
hired_on DATE NOT NULL
);
INSERT INTO employees (name, department, salary, manager_id, hired_on) VALUES
('Asha Rao', 'management', 150000.00, NULL, '2020-01-15'),
('Ben Carter', 'engineering', 120000.00, 1, '2021-03-01'),
('Chitra Nair', 'engineering', 95000.00, 2, '2022-07-11'),
('Dev Malhotra', 'engineering', 88000.00, 2, '2023-01-09'),
('Esha Gupta', 'sales', 70000.00, 1, '2021-09-20'),
('Farid Khan', 'sales', 64000.00, 5, '2022-11-02'),
('Gita Menon', 'sales', 64000.00, 5, '2024-02-14'),
('Hari Iyer', 'support', 52000.00, 1, '2023-05-30');
query.sql
SQL
SELECT name, salary, NTILE(4) OVER (ORDER BY salary DESC) AS quartile
FROM employees
ORDER BY salary DESC, name;
Output
OUTPUT
+--------------+-----------+----------+
| name | salary | quartile |
+--------------+-----------+----------+
| Asha Rao | 150000.00 | 1 |
| Ben Carter | 120000.00 | 1 |
| Chitra Nair | 95000.00 | 2 |
| Dev Malhotra | 88000.00 | 2 |
| Esha Gupta | 70000.00 | 3 |
| Farid Khan | 64000.00 | 3 |
| Gita Menon | 64000.00 | 4 |
| Hari Iyer | 52000.00 | 4 |
+--------------+-----------+----------+
8 rows in setEight employees, four buckets, so two per bucket: the best-paid two are in quartile 1, the next two in quartile 2, and so on.
Uneven counts
When the row count is not divisible by n, the first buckets get one extra row:
query.sql
SQL
SELECT name, salary, NTILE(3) OVER (ORDER BY salary DESC) AS tertile FROM employees ORDER BY salary DESC, name;
Output
OUTPUT
+--------------+-----------+---------+
| name | salary | tertile |
+--------------+-----------+---------+
| Asha Rao | 150000.00 | 1 |
| Ben Carter | 120000.00 | 1 |
| Chitra Nair | 95000.00 | 1 |
| Dev Malhotra | 88000.00 | 2 |
| Esha Gupta | 70000.00 | 2 |
| Farid Khan | 64000.00 | 2 |
| Gita Menon | 64000.00 | 3 |
| Hari Iyer | 52000.00 | 3 |
+--------------+-----------+---------+
8 rows in set8 rows in 3 buckets: the sizes are 3, 3 and 2.
query.sql
SQL
SELECT bucket, COUNT(*) AS employees, MIN(salary) AS lowest, MAX(salary) AS highest
FROM (SELECT salary, NTILE(3) OVER (ORDER BY salary DESC) AS bucket FROM employees) AS t
GROUP BY bucket
ORDER BY bucket;
Output
OUTPUT
+--------+-----------+----------+-----------+
| bucket | employees | lowest | highest |
+--------+-----------+----------+-----------+
| 1 | 3 | 95000.00 | 150000.00 |
| 2 | 3 | 64000.00 | 88000.00 |
| 3 | 2 | 52000.00 | 64000.00 |
+--------+-----------+----------+-----------+
3 rows in setEqual-sized, not equal-valued
NTILE splits by row count, not by value ranges, and it splits tied rows too. Two people with the same salary can land in different buckets (Farid and Gita above might be split). If tied rows must stay together, use PERCENT_RANK() or CUME_DIST() with a threshold (RANK).
Customer segments
A classic use is segmentation: top 20 percent of customers by spending, and so on. NTILE(5) gives quintiles:
query.sql
SQL
CREATE TABLE customer_spend (customer VARCHAR(10), spent INT);
INSERT INTO customer_spend VALUES ('c1', 900), ('c2', 120), ('c3', 560), ('c4', 40), ('c5', 300), ('c6', 75), ('c7', 650), ('c8', 220), ('c9', 15), ('c10', 480);
SELECT customer, spent,
CASE NTILE(5) OVER (ORDER BY spent DESC) WHEN 1 THEN 'top 20%' WHEN 2 THEN 'upper' WHEN 3 THEN 'middle' WHEN 4 THEN 'lower' ELSE 'bottom 20%' END AS segment
FROM customer_spend
ORDER BY spent DESC;
Output
OUTPUT
+----------+-------+------------+
| customer | spent | segment |
+----------+-------+------------+
| c1 | 900 | top 20% |
| c7 | 650 | top 20% |
| c3 | 560 | upper |
| c10 | 480 | upper |
| c5 | 300 | middle |
| c8 | 220 | middle |
| c2 | 120 | lower |
| c6 | 75 | lower |
| c4 | 40 | bottom 20% |
| c9 | 15 | bottom 20% |
+----------+-------+------------+
10 rows in setBuckets per group
query.sql
SQL
SELECT department, name, salary, NTILE(2) OVER (PARTITION BY department ORDER BY salary DESC) AS half
FROM employees
WHERE department IN ('engineering', 'sales')
ORDER BY department, salary DESC;
Output
OUTPUT
+-------------+--------------+-----------+------+
| department | name | salary | half |
+-------------+--------------+-----------+------+
| engineering | Ben Carter | 120000.00 | 1 |
| engineering | Chitra Nair | 95000.00 | 1 |
| engineering | Dev Malhotra | 88000.00 | 2 |
| sales | Esha Gupta | 70000.00 | 1 |
| sales | Farid Khan | 64000.00 | 1 |
| sales | Gita Menon | 64000.00 | 2 |
+-------------+--------------+-----------+------+
6 rows in setWithin each department, the upper half is 1 and the lower half is 2. In a partition with an odd number of rows, the first bucket gets the extra row.
Percentiles with NTILE(100)
NTILE(100) gives percentile buckets:
query.sql
SQL
SELECT customer, spent, NTILE(100) OVER (ORDER BY spent) AS percentile_bucket FROM customer_spend ORDER BY spent;
Output
OUTPUT
+----------+-------+-------------------+
| customer | spent | percentile_bucket |
+----------+-------+-------------------+
| c9 | 15 | 1 |
| c4 | 40 | 2 |
| c6 | 75 | 3 |
| c2 | 120 | 4 |
| c8 | 220 | 5 |
| c5 | 300 | 6 |
| c10 | 480 | 7 |
| c3 | 560 | 8 |
| c7 | 650 | 9 |
| c1 | 900 | 10 |
+----------+-------+-------------------+
10 rows in setWith only 10 rows, each row gets its own bucket number. With thousands of rows, each bucket holds about 1 percent. For an exact median and percentiles, see Statistics.
A cut-off value per bucket
Find the lowest value of the top quartile:
query.sql
SQL
SELECT MIN(salary) AS top_quartile_starts_at
FROM (SELECT salary, NTILE(4) OVER (ORDER BY salary DESC) AS q FROM employees) AS t
WHERE q = 1;
Output
OUTPUT
+------------------------+
| top_quartile_starts_at |
+------------------------+
| 120000.00 |
+------------------------+
1 row in set
lightbulb
Use for grouping, not for exact statistics
NTILE is handy for “split into groups for a report”. For statistically exact percentiles with ties, use PERCENT_RANK() or a position-based calculation.