Using NTILE for Percentile Calculations

1. Why percentile analysis belongs directly in SQL

A percentile analysis answers questions like "which quarter of customers by revenue does this record fall into?" or "which response time marks the 95th percentile of all requests?". Such questions come up constantly in reporting, monitoring and customer segmentation, and without a database function for it, the calculation sooner or later ends up in a Python script with pandas or numpy that exports raw data from the database, sorts it and splits it into buckets.

SQL offers NTILE(n), a window function that solves exactly this problem without data ever leaving the database. NTILE(n) splits a result set ordered by a sort column into n roughly equal groups, so-called buckets, and assigns the bucket number to every row. That is the foundation for quartiles, deciles and any other percentile split, all in a single query.

This article walks through the mechanics of NTILE(n) in detail, how uneven bucket sizes come about, how NTILE differs from PERCENT_RANK and CUME_DIST, and what a practical customer segmentation by revenue quartile looks like.

2. NTILE(n): the basics of bucket distribution

NTILE(n) is a window function that expects the number of desired buckets as an argument, for example NTILE(4) for quartiles or NTILE(10) for deciles. Like every window function, NTILE requires an ORDER BY clause inside the OVER() definition, because without a defined order the assignment of rows to buckets would be arbitrary. Optionally, PARTITION BY groups the calculation so percentiles can be produced separately per category, region or time period.

The algorithm behind NTILE is simple: the database first counts all rows in the partition, divides that count by n, and assigns bucket 1 to the first rows in sorted order, bucket 2 to the next, and so on up to bucket n. With 100 rows and NTILE(4), exactly 25 rows land in each bucket, bucket 1 holds the 25 rows with the lowest values of the sort column, bucket 4 holds the 25 rows with the highest.

-- Basic NTILE: split customers into 4 buckets by total spend
SELECT
    customer_id,
    total_spend,
    NTILE(4) OVER (ORDER BY total_spend) AS spend_quartile
FROM customer_totals
ORDER BY total_spend;

-- Sample result (8 customers, 4 buckets, 2 rows each)
-- customer_id | total_spend | spend_quartile
-- 5           | 120.00      | 1
-- 2           | 340.00      | 1
-- 8           | 510.00      | 2
-- 1           | 680.00      | 2
-- 6           | 890.00      | 3
-- 3           | 1100.00     | 3
-- 4           | 1450.00     | 4
-- 7           | 2200.00     | 4

3. Uneven bucket sizes: how NTILE distributes remainders

If the row count is not evenly divisible by n, unequal bucket sizes are unavoidable. With 10 rows and NTILE(3), three exactly equal groups cannot be formed. NTILE solves this by distributing the remainder to the first buckets: with 10 rows and three buckets, the first two buckets get four rows each, the third bucket gets only two. This rule is fixed in the SQL standard and implemented identically across all mainstream databases.

This uneven distribution is usually harmless, but should be known before NTILE is applied to small data volumes in a report. With very few rows, say five rows and NTILE(10), the first five buckets get one row each and the remaining five buckets stay completely empty, meaning they don't even show up as their own bucket number in the result. For statistically reliable percentiles, NTILE therefore needs a sufficiently large data volume relative to the chosen bucket count.

-- Uneven distribution: 10 rows into 3 buckets
SELECT
    request_id,
    response_time_ms,
    NTILE(3) OVER (ORDER BY response_time_ms) AS bucket
FROM api_requests
ORDER BY response_time_ms;

-- 10 rows, 3 buckets: first two buckets get 4 rows, last gets 2
-- bucket 1: rows 1-4
-- bucket 2: rows 5-8
-- bucket 3: rows 9-10

4. Calculating quartiles: NTILE(4) in practice

Quartiles split a distribution into four equal groups, 25 percent of the rows each. NTILE(4) is therefore the direct SQL equivalent of a quartile: bucket 1 holds the lowest quartile, bucket 4 the highest. This split is the foundation for box plots, for identifying outliers, and for many business reports that want to split customers, products or transactions into "bottom", "lower middle", "upper middle" and "top" quarters.

A common follow-up step after computing quartiles is aggregation per bucket: the average, minimum and maximum of each quartile group show how much the values differ within the distribution. Combining NTILE(4) in a subquery or CTE with a subsequent GROUP BY spend_quartile aggregation produces a complete quartile overview in a single composed query.

-- Quartile summary: aggregate after bucketing
WITH quartiles AS (
    SELECT
        customer_id,
        total_spend,
        NTILE(4) OVER (ORDER BY total_spend) AS spend_quartile
    FROM customer_totals
)
SELECT
    spend_quartile,
    COUNT(*) AS customer_count,
    MIN(total_spend) AS min_spend,
    MAX(total_spend) AS max_spend,
    ROUND(AVG(total_spend), 2) AS avg_spend
FROM quartiles
GROUP BY spend_quartile
ORDER BY spend_quartile;

5. Deciles and percentiles: NTILE(10) and NTILE(100)

For finer splits, simply swap the argument passed to NTILE: NTILE(10) produces deciles with ten percent of rows per bucket, NTILE(100) produces true percentiles with one percent per bucket. This flexibility is one of the biggest advantages of NTILE over hard-coded case distinctions with CASE WHEN, where every boundary would have to be calculated and maintained manually.

NTILE(100) is especially common in performance monitoring, for example to identify the 95th or 99th percentile of response times. Instead of a fixed formula with PERCENTILE_CONT, which is not available in every database, NTILE(100) returns a bucket number per row, from which a simple filter WHERE percentile_bucket >= 95 extracts the top five percent of all requests, independent of the SQL dialect.

6. NTILE with PARTITION BY: percentiles per group

In practice, percentiles rarely need to be computed globally, but rather per category, for example revenue quartiles split by region or response-time percentiles split by API endpoint. PARTITION BY in the OVER() clause solves exactly that: NTILE then calculates the bucket assignment independently within each partition, every group gets its own four, ten or hundred buckets.

It matters here that the number of rows per partition directly affects how meaningful the buckets are. A region with only twelve customers and NTILE(10) produces buckets with only one or two customers each, which is barely statistically meaningful. Before applying PARTITION BY, it is therefore worth running a COUNT(*) per planned partition to check whether the chosen bucket count makes sense for that group size.

-- Quartiles per region, independent buckets in each partition
SELECT
    region,
    customer_id,
    total_spend,
    NTILE(4) OVER (
        PARTITION BY region
        ORDER BY total_spend
    ) AS regional_spend_quartile
FROM customer_totals
ORDER BY region, total_spend;

7. The difference to PERCENT_RANK and CUME_DIST

NTILE is not the only SQL function for distribution analysis. PERCENT_RANK() returns, for every row, a relative rank between 0 and 1, calculated as (rank - 1) / (total count - 1), while CUME_DIST() returns the cumulative distribution, the fraction of rows whose value is less than or equal to the current one. Both functions return a continuous number instead of a discrete bucket number and are therefore better suited when the exact relative rank is needed instead of a rough grouping.

The practical difference shows up most clearly with duplicates in the sort column: NTILE ignores equal values and splits strictly by row position, so two rows with an identical value can end up in different buckets. PERCENT_RANK and CUME_DIST, on the other hand, explicitly account for ties and assign equal values the same relative rank. Anyone working with many repeated values, for example ratings from one to five, should consider PERCENT_RANK instead of NTILE.

-- Compare NTILE, PERCENT_RANK and CUME_DIST on the same data
SELECT
    product_id,
    rating,
    NTILE(4) OVER (ORDER BY rating) AS ntile_bucket,
    ROUND(PERCENT_RANK() OVER (ORDER BY rating)::numeric, 3) AS percent_rank,
    ROUND(CUME_DIST() OVER (ORDER BY rating)::numeric, 3) AS cume_dist
FROM product_ratings
ORDER BY rating;

-- Note: rows with identical rating get identical percent_rank
-- and cume_dist, but can still fall into different ntile_bucket values

8. Use case: customer segmentation by revenue

A classic use case for NTILE is splitting customers into value segments for marketing or sales purposes. With NTILE(4) over total revenue per customer, a four-tier segmentation emerges that translates directly into a CASE WHEN clause, for example "top customers" for bucket 4 and "occasional buyers" for bucket 1. This segmentation adjusts automatically as the customer base changes, because NTILE recalculates the boundaries fresh from current data on every run, instead of using fixed revenue thresholds.

The advantage over hard-coded thresholds like "customers with revenue above 1000 euros" lies in the relative nature of NTILE: if average customer revenue grows over time, the quartile boundaries stay current automatically, without anyone having to manually adjust a threshold in the code. That makes NTILE-based segmentation considerably lower maintenance than rule sets with hard-wired numbers.

9. NTILE, PERCENT_RANK and CUME_DIST compared

The choice between NTILE, PERCENT_RANK and CUME_DIST depends on the desired result shape. If the application needs a clearly defined group membership, for example a dashboard with four customer segments, NTILE is the right choice, because it returns a discrete, directly filterable bucket number. If the application instead needs the exact relative rank of a single value, for example "this request is in the 97th percentile of all response times", PERCENT_RANK or CUME_DIST is more precise, because neither is constrained by the bucket boundaries of NTILE.

All three functions share the same basic requirement: an ORDER BY clause inside the OVER() definition, without which no meaningful distribution can be calculated. And all three can be combined with PARTITION BY to calculate percentiles separately per group, which makes them a consistent toolbox for nearly any distribution analysis in SQL.

10. Summary

NTILE(n) splits a sorted result set into n roughly equal buckets and is therefore the direct SQL tool for quartiles with NTILE(4), deciles with NTILE(10), and percentiles with NTILE(100). Remainder rows, when the row count is not evenly divisible, are distributed to the first buckets, which can lead to empty or unevenly sized buckets on small data volumes. Combined with PARTITION BY, NTILE calculates percentiles independently per group, for example per region or product category.

Compared to PERCENT_RANK and CUME_DIST, NTILE returns a discrete bucket number instead of a continuous relative rank and ignores ties in the process, while the two alternatives assign equal values the same rank. For classification tasks like customer segmentation, NTILE is the right choice, while PERCENT_RANK and CUME_DIST are preferable for precisely determining individual percentile thresholds.

11. FAQ: NTILE for Percentile Calculations