DocsWindow FunctionsNTILE

NTILE

Window Functions

Concept

NTILE(n) divides the ordered rows in a partition into n approximately equal groups (buckets) and assigns each row a bucket number from 1 to n. If the rows do not divide evenly, the earlier buckets get one extra row. NTILE is commonly used for percentile analysis, quartile assignments, and distributing work evenly.

For example, NTILE(4) creates quartiles: the top 25% of rows get bucket 1, the next 25% get bucket 2, and so on. NTILE(100) creates percentiles. This is useful for segmenting customers by spend level, products by popularity, or students by grade ranges.

NTILE works within PARTITION BY boundaries, so you can compute percentiles within each category, region, or time period independently. The ORDER BY in the OVER clause determines how rows are ranked before being divided into buckets.

Syntax

-- Quartiles (4 buckets)
SELECT
  name,
  price,
  NTILE(4) OVER (ORDER BY price) AS price_quartile
FROM products;

-- Deciles within each category
SELECT
  category,
  name,
  price,
  NTILE(10) OVER (
    PARTITION BY category ORDER BY price
  ) AS price_decile
FROM products;

Practical Example

Using the ecommerce schema (customers, orders, order_items, products)

-- Segment customers into spending tiers (quartiles)
SELECT
  customer,
  total_spent,
  CASE spend_quartile
    WHEN 1 THEN 'Bronze'
    WHEN 2 THEN 'Silver'
    WHEN 3 THEN 'Gold'
    WHEN 4 THEN 'Platinum'
  END AS tier
FROM (
  SELECT
    c.first_name || ' ' || c.last_name AS customer,
    SUM(o.total_amount) AS total_spent,
    NTILE(4) OVER (ORDER BY SUM(o.total_amount)) AS spend_quartile
  FROM customers c
  JOIN orders o ON o.customer_id = c.customer_id
  GROUP BY c.customer_id, c.first_name, c.last_name
) tiers
ORDER BY total_spent DESC;

-- Products in the top 10% by sales volume
SELECT name, units_sold
FROM (
  SELECT
    p.name,
    SUM(oi.quantity) AS units_sold,
    NTILE(10) OVER (ORDER BY SUM(oi.quantity) DESC) AS decile
  FROM products p
  JOIN order_items oi ON oi.product_id = p.product_id
  GROUP BY p.product_id, p.name
) ranked
WHERE decile = 1;

Common Pitfalls & Tips

1NTILE distributes rows as evenly as possible, but uneven division means some buckets have one more row than others. Do not assume exact equality.
2NTILE assigns bucket numbers based on row position, not value ranges. Two very different values can end up in the same bucket if they are adjacent in order.
3For true percentile values (not bucket numbers), use PERCENT_RANK() or CUME_DIST() instead of NTILE(100).

Practice NTILE queries