NTILE
IntermediateNTILE(n) distributes rows into n ranked buckets as evenly as possible, enabling quartile, decile, and percentile segmentation directly in SQL.
Overview
NTILE(n) assigns each row a bucket number from 1 to n based on its position in the window ORDER BY. If the number of rows is not evenly divisible by n, the first (remainder) buckets each get one extra row. NTILE is useful for business segmentation: divide customers into spending quartiles, identify the top 10 % by revenue, or rank salespeople into performance tiers. PERCENT_RANK and CUME_DIST are related functions: PERCENT_RANK returns (rank − 1) / (total rows − 1) as a 0-to-1 ratio, while CUME_DIST returns the fraction of rows with values less than or equal to the current row.
Customer Spending Quartiles with NTILE
NTILE(4) over total spend produces four equally-sized groups. Bucket 4 is the highest spenders (top quartile), bucket 1 is the lowest.
-- Divide customers into 4 spending quartiles
WITH customer_spend AS (
SELECT
u.id AS user_id,
u.name,
SUM(o.amount) AS total_spent
FROM users u
JOIN orders o ON o.user_id = u.id
WHERE o.status = 'completed'
GROUP BY u.id, u.name
)
SELECT
user_id,
name,
total_spent,
NTILE(4) OVER (ORDER BY total_spent ASC) AS spending_quartile
-- quartile 4 = top spenders, quartile 1 = lowest
FROM customer_spend
ORDER BY total_spent DESC;PERCENT_RANK for Top-10% Spenders
PERCENT_RANK returns a value between 0 and 1 representing the row's relative rank. Filter PERCENT_RANK >= 0.9 to get the top 10 % of spenders. CUME_DIST is similar but includes tied rows differently.
-- Identify top 10% spenders using PERCENT_RANK
WITH customer_spend AS (
SELECT
user_id,
SUM(amount) AS total_spent
FROM orders
WHERE status = 'completed'
GROUP BY user_id
),
ranked AS (
SELECT
user_id,
total_spent,
PERCENT_RANK() OVER (ORDER BY total_spent ASC) AS pct_rank,
CUME_DIST() OVER (ORDER BY total_spent ASC) AS cum_dist
FROM customer_spend
)
SELECT user_id, total_spent, ROUND(pct_rank * 100, 2) AS percentile
FROM ranked
WHERE pct_rank >= 0.90 -- top 10%
ORDER BY total_spent DESC;Sales Decile Analysis with NTILE(10)
Decile analysis (10 buckets) is common in marketing analytics. Combine NTILE with GROUP BY in an outer query to get aggregate metrics per decile.
-- Sales decile analysis: revenue and order count per decile
WITH product_revenue AS (
SELECT
p.id,
p.name,
p.category,
SUM(o.amount) AS total_revenue
FROM products p
JOIN orders o ON o.product_id = p.id
GROUP BY p.id, p.name, p.category
),
deciled AS (
SELECT
id,
name,
category,
total_revenue,
NTILE(10) OVER (ORDER BY total_revenue ASC) AS revenue_decile
FROM product_revenue
)
SELECT
revenue_decile,
COUNT(*) AS product_count,
SUM(total_revenue) AS decile_revenue,
AVG(total_revenue) AS avg_revenue
FROM deciled
GROUP BY revenue_decile
ORDER BY revenue_decile DESC;Key Points to Remember
- 1NTILE(n) assigns bucket numbers 1..n; when rows are not evenly divisible the first (mod) buckets receive one extra row.
- 2PERCENT_RANK = (rank − 1) / (total_rows − 1); it returns 0 for the first row and 1 for the last.
- 3CUME_DIST = number of rows with value <= current / total rows; it is always > 0 and <= 1.
- 4Filter PERCENT_RANK >= 0.9 to get the top 10 %; filter NTILE(10) = 10 for the equivalent decile.
- 5NTILE does not guarantee equal counts when rows are not divisible; the first buckets are larger by one.
- 6Always supply ORDER BY inside OVER() for NTILE, PERCENT_RANK, and CUME_DIST — results are meaningless without ordering.
Interview Questions
Sign in to ask AriaHow would you divide customers into 4 equal groups based on lifetime spend?
What is the difference between NTILE, PERCENT_RANK, and CUME_DIST?
How does NTILE handle remainder rows when the total count is not divisible by n?
Write a query to flag users in the top 20% by order count.
Ask Aria about NTILE
Your personal AI tutor — ask anything about this concept
Revision Status
Personal Notes
Sign in to save personal notes for this topic.
Discussion
Sign in to join the discussion.