SQL NTILE()

HR wants Beacon Robotics' employees split into 4 salary quartiles per department, top earners in bucket 1, lowest in bucket 4, for a compensation review. NTILE() does exactly that, no manual bucketing math required.

Learning Objectives

• Define NTILE(N) precisely, and the bucketing problem it solves.
• Write NTILE() with PARTITION BY and ORDER BY to build quartile-style groups.
• Explain exactly what happens to the remainder when rows don't divide evenly into N groups.

What NTILE() Actually Does

NTILE(N) divides the rows in a partition, in ORDER BY order, into N as-equal-as-possible buckets, labeling each row with its bucket number from 1 to N. It's the function behind quartiles (NTILE(4)), deciles (NTILE(10)), and any other even split into a fixed number of groups.

Splitting Employees Into Salary Quartiles

Within each department, the highest-paid employees land in quartile 1, the lowest-paid in quartile 4.

SELECT first_name, last_name, department_id, salary,
       NTILE(4) OVER (PARTITION BY department_id ORDER BY salary DESC) AS salary_quartile
FROM employees;

When Rows Don't Divide Evenly

11 employees split with NTILE(4) can't form four perfectly equal groups of 2.75. The standard behavior, the same on every database in this course, gives the extra rows to the earliest groups first: 3 rows in group 1, 3 in group 2, then 3 in group 3 (here you'd expect 2 each, but 11 ÷ 4 leaves 3 remainder across the first 3 groups), 2 in group 4. The exact pattern for 11 rows into 4 groups is 3, 3, 3, 2, the first three groups each absorb one extra row before settling into the even base size.

Try It Yourself

Write a query splitting all employees company-wide into 2 halves by salary (above-median vs below-median), on SQL Server.

SELECT first_name, salary,
       NTILE(2) OVER (ORDER BY salary DESC) AS salary_half
FROM employees;

Frequently Asked Questions

Common Beginner Mistakes

Best Practices

• Use NTILE() specifically for a fixed number of roughly-equal groups, like quartiles or deciles, not for finding a single cutoff value.
• Always pair NTILE() with an explicit ORDER BY, the bucketing is meaningless without one.
• Expect the first few buckets to carry any extra rows when the count doesn't divide evenly, don't assume perfectly equal sizes.

Interview Questions

Summary

NTILE(N) splits a partition's rows into N roughly equal buckets, numbered 1 to N in ORDER BY order, the standard tool behind quartiles, deciles, and similar fixed-count groupings. When the row count doesn't divide evenly, the earliest bucket numbers absorb the extra rows, identically across every database in this course.

What's Next?

SQL LAG() and LEAD() are next: looking at a preceding or following row's value directly, without a self-join.