SQL NTILE() Function
intermediatewindow-functions
2 min read
What is NTILE()?
NTILE(n) distributes rows into n roughly equal groups (buckets) and assigns each row a bucket number from 1 to n.
Syntax
NTILE(n) OVER (ORDER BY column)When to Use
- Creating quartiles (NTILE(4)) or deciles (NTILE(10))
- Splitting data into balanced segments for parallel processing
- Grading on a curve
Key Points
- Equal Groups — If rows divide evenly, each bucket has the same count. Otherwise, earlier buckets get one extra row.
- PARTITION BY — Partition to create buckets within each group.
- Quartiles —
NTILE(4)creates quartiles. - Percentiles —
NTILE(100)approximates percentile ranking. - Ordering Matters — The ORDER BY determines which rows go into which bucket.
Guided Practice
Solve the challenge below. Use hints when stuck and check your answer for instant feedback.
Practice challengeGuided learning mode
SQL NTILE() Function Challenge
Write a query that solve this task: divide employees into four salary groups.
Expected result
Each employee with a quartile number (1=top 25%, 4=bottom 25%).
Hidden checks
- Returned rows and values
- Output columns and result shape
- Final database state after the query runs
Lesson guidance
What is NTILE()?
Initializing database...Each run starts from fresh sample data.
More Examples
Split orders into 3 groups
Distribute orders evenly into three buckets by total.
Initializing database...Each run starts from fresh sample data.
Frequently Asked Questions
What happens when rows do not divide evenly into NTILE groups?
The first buckets get one extra row. NTILE(4) with 10 rows produces groups of 3, 3, 2, 2.
How is NTILE different from RANK?
RANK assigns a position. NTILE assigns a bucket number, distributing rows into N equal groups. NTILE tells you "which group," not "which position."