NTILE() — Percentile Buckets

NTILE(n) divides rows in a partition into n roughly equal buckets and assigns each row its bucket number. It's the simplest way to create percentiles, quartiles, or any fixed-size tier segmentation in SQL.

Basic syntax

NTILE(n) OVER (
    PARTITION BY partition_col   -- optional: bucket independently per group
    ORDER BY     sort_col        -- determines which rows go in which bucket
)

Divide customers into 4 quartiles by total spend:

WITH spend AS (
    SELECT
        o.customer_id,
        SUM(oi.quantity * oi.unit_price) AS total_spend
    FROM orders o
    JOIN order_items oi ON oi.order_id = o.order_id
    WHERE o.status = 'completed'
    GROUP BY o.customer_id
)
SELECT
    c.name,
    c.country,
    s.total_spend,
    NTILE(4) OVER (ORDER BY s.total_spend DESC) AS quartile
FROM spend s
JOIN customers c ON c.customer_id = s.customer_id
ORDER BY s.total_spend DESC;

Bucket 1 = top 25% spenders, bucket 4 = bottom 25%. The buckets are as equal in size as the row count allows.

How bucket sizes are calculated

With 50 rows and NTILE(4):

50 / 4 = 12 remainder 2
The first 2 buckets get 13 rows, the last 2 get 12 rows
Larger buckets always come first

With 7 rows and NTILE(3):

Buckets get 3, 2, 2 rows respectively

This is important: NTILE buckets are not based on value ranges — they're based on row counts. Two customers with very different spend can share a bucket; two customers with nearly identical spend can be in adjacent buckets if they straddle a boundary.

Deciles and percentiles

For 10 deciles use NTILE(10), for 100 percentiles use NTILE(100):

-- Assign each product to a revenue decile
WITH product_revenue AS (
    SELECT
        p.product_id,
        p.name,
        p.category,
        COALESCE(SUM(oi.quantity * oi.unit_price), 0) AS revenue
    FROM products p
    LEFT JOIN order_items oi ON oi.product_id = p.product_id
    GROUP BY p.product_id, p.name, p.category
)
SELECT
    name,
    category,
    revenue,
    NTILE(10) OVER (ORDER BY revenue DESC) AS decile
FROM product_revenue
ORDER BY decile, revenue DESC;

Decile 1 contains the top-10% revenue products. Useful for ABC analysis, inventory prioritisation, or reporting tiered metrics.

NTILE with PARTITION BY

Bucket independently within each group:

Purchase this course to unlock the full lesson.

← Previous Next →