NTILE Function in SQL
Overview
The NTILE(n) function is an analytical window function in SQL that divides an ordered partition of rows into $n$ approximately equal groups (buckets). It assigns an integer bucket number from $1$ to $n$ to each row. NTILE is widely utilized in data engineering, marketing segmentation, and quantitative finance to compute quartiles ($n=4$), quintiles ($n=5$), deciles ($n=10$), and percentiles ($n=100$) for ranking customers, balancing worker workloads, and segmenting statistical distributions.
What You Will Learn
In this lesson, you will master:
- Bucketing Mechanics: How NTILE distributes rows across buckets when row counts are not evenly divisible.
- Quartile & Decile Segmentation: Categorizing high, medium, and low value transactions.
- Partitioned Bucketing: Segmenting metrics independently within categories (e.g., salary tiers per department).
- NTILE vs CUME_DIST vs DENSE_RANK: Knowing when to use integer buckets vs exact mathematical cumulative distributions.
- Ties & Determinism: Preventing inconsistent bucket assignments when rows share identical metric values.
How NTILE Distributes Uneven Rows
When the total number of rows in a partition is not evenly divisible by $n$:
- Python and SQL engines assign the extra remainder rows to the lowest-numbered buckets first.
- Each bucket size will differ by at most 1 row.
Example: 10 Rows Divided into 4 Buckets (NTILE(4))
$10 \div 4 = 2$ rows per bucket, with a remainder of 2 rows. The remainder of 2 rows is distributed to Bucket 1 and Bucket 2:
- Bucket 1: 3 rows
- Bucket 2: 3 rows
- Bucket 3: 2 rows
- Bucket 4: 2 rows
SELECT
student_id,
test_score,
NTILE(4) OVER (ORDER BY test_score DESC) AS score_quartile
FROM student_scores;
Practical Application: Customer Spending Quartiles
Categorizing customers into quartiles for targeted marketing promotions:
WITH customer_totals AS (
SELECT
customer_id,
SUM(order_total) AS total_spend
FROM orders
GROUP BY customer_id
),
customer_quartiles AS (
SELECT
customer_id,
total_spend,
NTILE(4) OVER (ORDER BY total_spend DESC) AS quartile
FROM customer_totals
)
SELECT
customer_id,
total_spend,
quartile,
CASE quartile
WHEN 1 THEN 'Tier 1: Platinum (Top 25%)'
WHEN 2 THEN 'Tier 2: Gold (50%-75%)'
WHEN 3 THEN 'Tier 3: Silver (25%-50%)'
WHEN 4 THEN 'Tier 4: Bronze (Lowest 25%)'
END AS customer_tier
FROM customer_quartiles
ORDER BY total_spend DESC;
Partitioned NTILE: Salary Bands per Department
To bucket employees within their respective departments rather than across the whole company:
SELECT
department_id,
employee_id,
last_name,
salary,
-- Divide employees in EACH department into 3 compensation bands
NTILE(3) OVER (
PARTITION BY department_id
ORDER BY salary DESC
) AS dept_salary_tier
FROM employees
ORDER BY department_id, salary DESC;
NTILE vs CUME_DIST vs PERCENT_RANK: Key Differences
| Function | Output Format | Handling of Ties | Best Use Case |
| :--- | :--- | :--- | :--- |
| NTILE(n) | Integer from $1$ to $n$ | Arbitrarily splits tied rows across buckets to preserve bucket size balance | Workload balancing, batch chunking, quartile tags |
| CUME_DIST() | Continuous Float ($0.0 < x \le 1.0$) | Rows with identical values receive the exact same cumulative distribution | Statistical significance, empirical CDFs |
| PERCENT_RANK()| Continuous Float ($0.0 \le x \le 1.0$) | Evaluates relative rank: (rank - 1) / (total_rows - 1) | Standardized percentile rankings |
NTILE and Identical Values
Because NTILE prioritizes keeping bucket counts equal, two employees with the exact same salary ($100,000) might end up in Bucket 1 and Bucket 2 respectively if the boundary falls between them! To make assignments deterministic, always include a unique tie-breaker column: ORDER BY salary DESC, employee_id ASC.
Common Mistakes & Gotchas
- Requesting More Buckets than Rows: If a table has 5 rows and you request
NTILE(10), the function fills Buckets 1 through 5 with 1 row each, and Buckets 6 through 10 remain empty. - Filtering by NTILE in the WHERE Clause: Because window functions execute after the
WHEREclause, you cannot writeWHERE NTILE(4) ... = 1. You must wrap the query in a Common Table Expression (CTE) or subquery.
Interview Perspective
SQL Interview Question: How does NTILE handle row remainders, and why can two identical values be placed in different buckets?
Key Talking Points for Candidates:
- Remainder Distribution: If $N$ rows are divided into $K$ buckets, the base size is $\lfloor N / K \rfloor$. The remainder $N \pmod K$ extra rows are added one-by-one to the first remainder buckets.
- Non-Tied Enforcement: Unlike
DENSE_RANK(),NTILEdoes not group tied values together. It enforces strict bucket capacity, which can push tied values across bucket boundaries unlessCUME_DISTorPERCENT_RANKis used instead.
Interactive Practice Challenge
DataPlayArena Technical Editorial Board
Curated & maintained by enterprise data platform engineers with 10+ years in distributed systems.
Runtime Verification Standards: Every code example, query, and architecture diagram in this tutorial is compiled and verified against real runtimes (Apache Spark 3.5, Python 3.11, PostgreSQL, and DuckDB). If you spot any errata or want to suggest improvements, please submit feedback.