window

NTILE(): Splitting Rows Into Buckets

Use NTILE(4) to assign employees into salary quartiles.

Overview

NTILE(n) divides the ordered rows of a window into n roughly equal buckets, assigning each row a bucket number from 1 to n. It's the SQL way to compute percentiles/quartiles/tertiles for leaderboards and segmentation.

The Query

Loading playground environment...

Step-by-Step Breakdown

NTILE(4): splits employees into 4 buckets (quartiles) by salary.
OVER (ORDER BY salary DESC): bucket 1 holds the highest-paid quarter.
If rows don't divide evenly, the earlier buckets get the extra rows.

NTILE vs manual thresholds

NTILE is order-based, not value-based, bucket edges depend on row counts, not fixed salary cutoffs. Use it for ranking segments, not precise ranges.

Variations

Split movies into 3 budgets tiers (tertiles):

Loading playground environment...

Common Mistakes

Assuming equal value ranges. Buckets are sized by row count, so the salary gap between bucket 1 and 2 may differ from 2 to 3.
Non-deterministic ties. Rows with equal ORDER BY values may land in different buckets arbitrarily.
Forgetting ORDER BY. NTILE without an order has undefined bucket assignment.

Related examples

DENSE_RANK(): Ranking Without Gaps

window

Rank employees by salary company-wide using DENSE_RANK, which never skips numbers after ties.

ROW_NUMBER(): Enumerating Rows Within Groups

window

Assign a sequential number to each employee within their department, ordered by salary.

Window Functions: RANK() and PARTITION BY

window

Rank employees by salary within their own department using RANK() OVER (PARTITION BY ...), without collapsing rows the way GROUP BY does.

Cite this resource

SQLSimplified. "NTILE(): Splitting Rows Into Buckets Example". Available at: https://sqlsimplified.online/examples/ntile