intermediate

NTILE Function in SQL

By DataPlayArena Technical Review BoardUpdated 2026-07-208 min readRuntime Verified

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
sql
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:

sql
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:

sql
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 WHERE clause, you cannot write WHERE 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:

  1. 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.
  2. Non-Tied Enforcement: Unlike DENSE_RANK(), NTILE does not group tied values together. It enforces strict bucket capacity, which can push tied values across bucket boundaries unless CUME_DIST or PERCENT_RANK is used instead.

Interactive Practice Challenge

Challenge: Decile Performance Segmentation

Write a query on server_metrics that buckets all servers into 10 deciles (NTILE(10)) ordered by cpu_utilization_pct descending. Return only servers in the top decile (Decile 1) that require immediate autoscaling attention.

DPA

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.