How to Use NTILE in MySQL
NTILE is a window function that divides your result set into a specified number of approximately equal groups, or 'tiles.' Think of it as automatically creating buckets for your data based on...
Key Insights
- NTILE divides result sets into equal-sized buckets, making it essential for percentile analysis, customer segmentation, and performance ranking
- Unlike RANK() or ROW_NUMBER(), NTILE guarantees balanced distribution across groups, with MySQL automatically handling remainders by adding extra rows to lower-numbered buckets
- Combining NTILE with PARTITION BY enables sophisticated analysis like creating quartiles per department or segmenting customers by region
Understanding NTILE and When to Use It
NTILE is a window function that divides your result set into a specified number of approximately equal groups, or “tiles.” Think of it as automatically creating buckets for your data based on ordering criteria. If you need to identify the top 25% of customers by revenue, divide inventory into ABC categories, or create salary quartiles, NTILE is your tool.
The function assigns a bucket number (1 through n) to each row, ensuring balanced distribution. This differs fundamentally from other ranking functions—NTILE focuses on group assignment rather than individual ranking positions.
Common scenarios where NTILE excels include:
- Quartile or percentile analysis for business metrics
- Customer segmentation (high/medium/low value tiers)
- Performance evaluation (top/middle/bottom performers)
- ABC inventory classification
- Market basket analysis with price tiers
Basic NTILE Syntax
The fundamental NTILE syntax follows the standard window function pattern:
NTILE(n) OVER (ORDER BY column [ASC|DESC])
The n parameter specifies how many buckets to create, and the ORDER BY clause determines how rows are distributed. Here’s a practical example using employee salary data:
SELECT
employee_id,
employee_name,
salary,
NTILE(4) OVER (ORDER BY salary DESC) AS salary_quartile
FROM employees;
This query divides employees into four equal groups based on salary. The highest earners receive quartile 1, the next group gets quartile 2, and so on. The output might look like:
employee_id | employee_name | salary | salary_quartile
------------|---------------|---------|----------------
101 | Sarah Chen | 150000 | 1
102 | Mike Johnson | 145000 | 1
103 | Anna Smith | 120000 | 2
104 | Tom Brown | 115000 | 2
105 | Lisa Davis | 95000 | 3
106 | John Wilson | 90000 | 3
107 | Emma Garcia | 75000 | 4
108 | David Miller | 70000 | 4
Combining NTILE with PARTITION BY
The real power of NTILE emerges when you combine it with PARTITION BY, allowing you to create buckets within specific groups. This is invaluable for comparative analysis across departments, regions, or categories.
SELECT
product_id,
product_name,
category,
sales_amount,
NTILE(3) OVER (
PARTITION BY category
ORDER BY sales_amount DESC
) AS performance_tier
FROM product_sales
WHERE sale_year = 2024;
This query creates three performance tiers (terciles) for each product category independently. A product in tier 1 within Electronics might have lower absolute sales than a tier 3 product in Furniture, but it’s still a top performer within its category.
Here’s a more complex example analyzing sales representatives:
SELECT
rep_id,
rep_name,
region,
total_sales,
NTILE(5) OVER (
PARTITION BY region
ORDER BY total_sales DESC
) AS quintile,
CONCAT(
CASE NTILE(5) OVER (PARTITION BY region ORDER BY total_sales DESC)
WHEN 1 THEN 'Top 20%'
WHEN 2 THEN 'Second 20%'
WHEN 3 THEN 'Middle 20%'
WHEN 4 THEN 'Fourth 20%'
WHEN 5 THEN 'Bottom 20%'
END
) AS performance_label
FROM sales_reps
WHERE year = 2024;
Real-World Applications
Let’s explore practical scenarios where NTILE delivers immediate value.
Customer Segmentation for Targeted Marketing:
SELECT
customer_id,
customer_name,
total_lifetime_value,
NTILE(3) OVER (ORDER BY total_lifetime_value DESC) AS customer_segment,
CASE NTILE(3) OVER (ORDER BY total_lifetime_value DESC)
WHEN 1 THEN 'High Value - VIP Treatment'
WHEN 2 THEN 'Medium Value - Standard Engagement'
WHEN 3 THEN 'Low Value - Automation Focus'
END AS segment_strategy
FROM customer_metrics;
This segments customers into three tiers, enabling different marketing strategies and resource allocation for each group.
ABC Inventory Classification:
SELECT
sku,
product_name,
annual_revenue,
inventory_cost,
NTILE(3) OVER (ORDER BY annual_revenue DESC) AS abc_class,
CASE NTILE(3) OVER (ORDER BY annual_revenue DESC)
WHEN 1 THEN 'A - Tight Control, Weekly Review'
WHEN 2 THEN 'B - Moderate Control, Monthly Review'
WHEN 3 THEN 'C - Basic Control, Quarterly Review'
END AS management_approach
FROM inventory_analysis
WHERE year = 2024;
This implements the classic ABC analysis where A items (top ~20%) generate most revenue and deserve closest attention.
Performance Distribution Analysis:
SELECT
department,
decile,
COUNT(*) AS employee_count,
MIN(performance_score) AS min_score,
MAX(performance_score) AS max_score,
AVG(performance_score) AS avg_score
FROM (
SELECT
employee_id,
department,
performance_score,
NTILE(10) OVER (ORDER BY performance_score DESC) AS decile
FROM employee_performance
WHERE review_year = 2024
) AS scored_employees
GROUP BY department, decile
ORDER BY department, decile;
This creates a decile distribution showing how employees spread across performance levels, useful for identifying departments with skewed performance distributions.
NTILE vs Other Ranking Functions
Understanding the differences between NTILE and similar functions prevents misuse:
SELECT
employee_name,
salary,
NTILE(4) OVER (ORDER BY salary DESC) AS ntile_quartile,
RANK() OVER (ORDER BY salary DESC) AS rank_position,
DENSE_RANK() OVER (ORDER BY salary DESC) AS dense_rank_position,
ROW_NUMBER() OVER (ORDER BY salary DESC) AS row_num
FROM employees
ORDER BY salary DESC;
Sample output:
employee_name | salary | ntile_quartile | rank_position | dense_rank | row_num
--------------|---------|----------------|---------------|------------|--------
Sarah Chen | 150000 | 1 | 1 | 1 | 1
Mike Johnson | 150000 | 1 | 1 | 1 | 2
Anna Smith | 120000 | 2 | 3 | 2 | 3
Tom Brown | 120000 | 2 | 3 | 2 | 4
When to use each:
- NTILE: Need equal-sized groups regardless of value gaps (customer segments, performance tiers)
- RANK: Want to preserve gaps for tied values (competition rankings, leaderboards)
- DENSE_RANK: Need consecutive rankings without gaps (grade assignments)
- ROW_NUMBER: Require unique sequential numbers (pagination, ordering)
Handling Edge Cases and Best Practices
Uneven Distribution: When rows don’t divide evenly, MySQL assigns extra rows to lower-numbered buckets:
-- 10 rows divided into 3 tiles
SELECT
id,
value,
NTILE(3) OVER (ORDER BY value) AS tile
FROM (
SELECT 1 AS id, 10 AS value UNION ALL
SELECT 2, 20 UNION ALL SELECT 3, 30 UNION ALL
SELECT 4, 40 UNION ALL SELECT 5, 50 UNION ALL
SELECT 6, 60 UNION ALL SELECT 7, 70 UNION ALL
SELECT 8, 80 UNION ALL SELECT 9, 90 UNION ALL
SELECT 10, 100
) AS sample_data;
Result: Tile 1 gets 4 rows, Tiles 2 and 3 each get 3 rows (4-3-3 distribution).
NULL Handling: NULLs in the ORDER BY column appear first (ASC) or last (DESC):
SELECT
product_id,
sales_amount,
NTILE(4) OVER (ORDER BY sales_amount DESC NULLS LAST) AS quartile
FROM products;
Performance Considerations: NTILE requires sorting the entire result set. For large tables:
-- Add index on the ORDER BY column
CREATE INDEX idx_sales_amount ON products(sales_amount);
-- Consider filtering before applying NTILE
SELECT * FROM (
SELECT
product_id,
sales_amount,
NTILE(4) OVER (ORDER BY sales_amount DESC) AS quartile
FROM products
WHERE is_active = 1
AND sale_date >= DATE_SUB(CURDATE(), INTERVAL 1 YEAR)
) AS ranked_products
WHERE quartile = 1;
Avoid Recalculation: If using the same NTILE calculation multiple times, use a CTE:
WITH customer_tiers AS (
SELECT
customer_id,
revenue,
NTILE(3) OVER (ORDER BY revenue DESC) AS tier
FROM customer_revenue
)
SELECT
tier,
COUNT(*) AS customer_count,
SUM(revenue) AS tier_revenue,
AVG(revenue) AS avg_revenue
FROM customer_tiers
GROUP BY tier;
Putting NTILE to Work
NTILE transforms raw data into actionable segments. Whether you’re building customer tiers for personalized marketing, creating performance bands for employee evaluation, or implementing inventory classification systems, NTILE provides the balanced distribution you need.
The key is understanding that NTILE prioritizes equal group sizes over value boundaries. This makes it perfect for percentage-based analysis (top 25%, bottom 10%) but less suitable when specific value thresholds matter. Master the combination of NTILE with PARTITION BY, and you’ll unlock powerful segmentation capabilities across any dimension of your data.
Start by identifying business questions that involve “top X%” or “divide into N groups”—those are your NTILE opportunities. The function’s simplicity belies its analytical power.