In this lesson, you will learn how to partition your entire dataset into ten equal-sized segments, known as deciles, to objectively categorize performance tiers.
The Logic of Deciles
When analyzing large datasets—like customer lifetime value, employee sales performance, or website traffic—looking at simple averages often hides the truth. By segmenting data into deciles, we divide our population into ten ranked groups, each containing roughly 10% of the total records. The first decile contains the bottom 10%, while the tenth decile contains the top 10%. This allows you to pinpoint exactly where your high performers (top decile) and underperformers (bottom decile) fall.
Implementing with NTILE
In SQL, we use the NTILE(n) window function to handle this distribution. By passing 10 as the argument to NTILE, the database automatically assigns a rank from 1 to 10 to every row based on the column you specify in the ORDER BY clause.
Here is how you might categorize a list of sales representatives based on their total revenue:
SELECT
sales_rep_name,
total_revenue,
NTILE(10) OVER (ORDER BY total_revenue DESC) AS performance_decile
FROM sales_records;
Interpreting the Results
In this query, the ORDER BY total_revenue DESC ensures that the "top" performers get a decile of 1. If you wanted the bottom 10% to be decile 1, you would simply remove the DESC keyword.
Once the data is segmented, you can perform further analysis on these groups. For example, you could wrap this query in a Common Table Expression (CTE) and then perform an AVG(total_revenue) grouped by the performance_decile to see the revenue thresholds for each tier. This reveals the "gap" between your average performers and your top 10% elite, providing actionable insights into performance distribution that simple averages simply cannot provide.
Key Takeaway: Use NTILE(10) to rank-order your data into ten distinct tiers, allowing you to isolate and compare the top and bottom segments of your population.