NTILE()
SQL · Window Functions
Theory
NTILE(n) splits the ordered rows of a partition into n buckets that are as equal in size as possible, and returns the bucket number (1 to n) for each row. NTILE(4) gives quartiles, NTILE(10) gives deciles. Bucket 1 holds the first rows in the window ORDER BY, so with ORDER BY salary bucket 1 is the lowest-paid group.
When the rows do not divide evenly, the earlier buckets get one extra row each. With 6 rows and NTILE(4), 6 / 4 = 1 remainder 2, so buckets 1 and 2 get 2 rows and buckets 3 and 4 get 1 row. NTILE works on row counts, not values, so two people with the same salary can land in different buckets, and with fewer rows than buckets some buckets are just empty. If you need value-based cut-offs, use PERCENT_RANK or percentile functions instead.
Why we use it
Analysts often need to segment a list into equal-sized groups: top 25% customers by spend, salary quartiles, deciles for a lift chart. NTILE does this in one expression without calculating cut-off values first.
When to use it
Use NTILE for equal-count segments such as quartiles or A/B/C customer tiers. If ties must stay in the same group, or you need true value percentiles, use PERCENT_RANK, CUME_DIST or PERCENTILE_CONT instead.
How it works
- Rows sorted by the window ORDER BY
- Count rows in the partition (N) and read bucket count (n)
- Base size = N / n, remainder r = N mod n
- First r buckets get base size + 1 rows
- Fill buckets in order and label rows 1..n
- Each row has a bucket number
Syntax
NTILE(n) OVER (ORDER BY col)Example
SELECT name, salary,
NTILE(4) OVER (ORDER BY salary) AS quartile
FROM employees;Key points
- Great for quartiles, quintiles, deciles and tiering
- Bucket sizes differ by at most one row; earlier buckets get the extra
- Based on row count, not values: equal values can split across buckets
- Bucket 1 = first rows in the ORDER BY (use DESC for top tier = 1)
- Add a tie-breaker so bucket assignment is repeatable