Statistical Distribution Functions: NTILE, CUME_DIST, PERCENT_RANK0%

Statistical Distribution Functions: NTILE, CUME_DIST, PERCENT_RANK

Advanced13 min read•Updated: 2026-09-12

Statistical Distribution Functions: NTILE, CUME_DIST, PERCENT_RANK

Modern enterprise analytics frequently requires statistical distribution modeling:

Segmenting customers into spending deciles or quartiles.
Calculating what percentage of employees earn less than or equal to a given salary.
Generating relative percentile ranks for credit risk scoring.

MySQL 8.0 provides three powerful statistical distribution window functions: NTILE(), CUME_DIST(), and PERCENT_RANK().

1. NTILE(n): Equal Bucket Distribution

NTILE(n) divides an ordered partition into n roughly equal buckets and assigns the bucket number (from 1 to n) to each row:

1-- Segment customers into 4 spending quartiles
2SELECT 
3    customer_id,
4    annual_spend,
5    NTILE(4) OVER (ORDER BY annual_spend DESC) AS spend_quartile
6FROM customer_totals;

Bucket 1: Top 25% (VIPs)
Bucket 2: Upper Mid 25%
Bucket 3: Lower Mid 25%
Bucket 4: Bottom 25%

Rule: If the row count is not evenly divisible by n, earlier buckets receive the extra rows. (e.g., 10 rows into 4 buckets gives bucket sizes: 3, 3, 2, 2).

2. CUME_DIST(): Cumulative Distribution

CUME_DIST() calculates the cumulative distribution of a value within a partition. It represents the proportion of rows with values less than or equal to the current row's value:

$$ ext{CUME_DIST} = rac{ ext{Number of rows with value} le ext{current value}}{ ext{Total rows in partition}}$$

1SELECT 
2    student_name,
3    exam_score,
4    ROUND(CUME_DIST() OVER (ORDER BY exam_score ASC), 4) AS cume_dist
5FROM exam_results;

Output Values:

A CUME_DIST of 0.80 indicates that 80% of students scored less than or equal to this student's score. The value ranges from > 0.0 to 1.0.

3. PERCENT_RANK(): Relative Percentile Rank

PERCENT_RANK() computes the relative rank of a row within a partition as a fraction between 0.0 and 1.0 using the formula:

$$ ext{PERCENT_RANK} = rac{ ext{Rank} - 1}{ ext{Total Rows} - 1}$$

The highest-ranked (or first) row always has PERCENT_RANK = 0.0.
The lowest-ranked (or last) row always has PERCENT_RANK = 1.0.

1SELECT 
2    employee_name,
3    salary,
4    ROUND(PERCENT_RANK() OVER (ORDER BY salary ASC), 4) AS salary_percentile
5FROM employees;

Summary Comparison Table

Function	Output Range	Key Characteristic	Common Use Case
`NTILE(n)`	1 to `n` (Integers)	Divides rows into equal discrete groups	Quartiles, deciles, tiering
`CUME_DIST()`	`> 0.0` to `1.0` (Float)	Fraction of rows $le$ current row	Cumulative probability, percentiles
`PERCENT_RANK()`	`0.0` to `1.0` (Float)	Relative rank score based on `(rank - 1)/(N - 1)`	Standardized scoring, benchmarks

Interactive Quiz• Knowledge Check

Multiple Choice Questions

Question 1

How does NTILE(4) distribute 10 rows across its buckets?

Question 2

What is the mathematical range of CUME_DIST()?

Question 3

What is the value of PERCENT_RANK() for the very first row in an ordered partition?

Question 4

If an employee has a CUME_DIST() of 0.75 on salary, what does this indicate?

Question 5

Which function is ideal for categorizing customers into top 10% (deciles) for loyalty marketing campaigns?

Next Lesson

B-Tree vs Hash Index Data Structures

Continue learning with hands-on practice, examples, and exercises in the upcoming topic.

Related Lessons

Previous Lesson	Next Lesson
Value Boundary Functions: FIRST_VALUE, LAST_VALUE, NTH_VALUE	B-Tree vs Hash Index Data Structures

Practice Quiz

Test your understanding of this lesson with 5 questions. Each question has one correct answer.

Previous Next

PreviousPrevNext

MSK Institute • Shikohabad

Loading Page Content...

Preparing courses, live labs, and learning resources.सामग्री लोड हो रही है, कृपया प्रतीक्षा करें...

Connecting serverPlease wait...

💻 Practical Labs🚀 Free Demo📜 Verified Certificates

B-Tree vs Hash Index Data Structures

Statistical Distribution Functions: NTILE, CUME_DIST, PERCENT_RANK

Advanced13 min read•Updated: 2026-09-12

Study Materials Cheatsheet

Statistical Distribution Functions: NTILE, CUME_DIST, PERCENT_RANK

Modern enterprise analytics frequently requires statistical distribution modeling:

Segmenting customers into spending deciles or quartiles.
Calculating what percentage of employees earn less than or equal to a given salary.
Generating relative percentile ranks for credit risk scoring.

MySQL 8.0 provides three powerful statistical distribution window functions: NTILE(), CUME_DIST(), and PERCENT_RANK().

1. NTILE(n): Equal Bucket Distribution

NTILE(n) divides an ordered partition into n roughly equal buckets and assigns the bucket number (from 1 to n) to each row:

1-- Segment customers into 4 spending quartiles
2SELECT 
3    customer_id,
4    annual_spend,
5    NTILE(4) OVER (ORDER BY annual_spend DESC) AS spend_quartile
6FROM customer_totals;

Bucket 1: Top 25% (VIPs)
Bucket 2: Upper Mid 25%
Bucket 3: Lower Mid 25%
Bucket 4: Bottom 25%

Rule: If the row count is not evenly divisible by n, earlier buckets receive the extra rows. (e.g., 10 rows into 4 buckets gives bucket sizes: 3, 3, 2, 2).

2. CUME_DIST(): Cumulative Distribution

CUME_DIST() calculates the cumulative distribution of a value within a partition. It represents the proportion of rows with values less than or equal to the current row's value:

$$ ext{CUME_DIST} = rac{ ext{Number of rows with value} le ext{current value}}{ ext{Total rows in partition}}$$

1SELECT 
2    student_name,
3    exam_score,
4    ROUND(CUME_DIST() OVER (ORDER BY exam_score ASC), 4) AS cume_dist
5FROM exam_results;

Output Values:

A CUME_DIST of 0.80 indicates that 80% of students scored less than or equal to this student's score. The value ranges from > 0.0 to 1.0.

3. PERCENT_RANK(): Relative Percentile Rank

PERCENT_RANK() computes the relative rank of a row within a partition as a fraction between 0.0 and 1.0 using the formula:

$$ ext{PERCENT_RANK} = rac{ ext{Rank} - 1}{ ext{Total Rows} - 1}$$

The highest-ranked (or first) row always has PERCENT_RANK = 0.0.
The lowest-ranked (or last) row always has PERCENT_RANK = 1.0.

1SELECT 
2    employee_name,
3    salary,
4    ROUND(PERCENT_RANK() OVER (ORDER BY salary ASC), 4) AS salary_percentile
5FROM employees;

Summary Comparison Table

Function	Output Range	Key Characteristic	Common Use Case
`NTILE(n)`	1 to `n` (Integers)	Divides rows into equal discrete groups	Quartiles, deciles, tiering
`CUME_DIST()`	`> 0.0` to `1.0` (Float)	Fraction of rows $le$ current row	Cumulative probability, percentiles
`PERCENT_RANK()`	`0.0` to `1.0` (Float)	Relative rank score based on `(rank - 1)/(N - 1)`	Standardized scoring, benchmarks

Interactive Quiz• Knowledge Check

Multiple Choice Questions

Question 1

How does NTILE(4) distribute 10 rows across its buckets?

Question 2

What is the mathematical range of CUME_DIST()?

Question 3

What is the value of PERCENT_RANK() for the very first row in an ordered partition?

Question 4

If an employee has a CUME_DIST() of 0.75 on salary, what does this indicate?

Question 5

Which function is ideal for categorizing customers into top 10% (deciles) for loyalty marketing campaigns?

Next Lesson

B-Tree vs Hash Index Data Structures

Continue learning with hands-on practice, examples, and exercises in the upcoming topic.

Related Lessons

Previous Lesson	Next Lesson
Value Boundary Functions: FIRST_VALUE, LAST_VALUE, NTH_VALUE	B-Tree vs Hash Index Data Structures

Practice Quiz

Test your understanding of this lesson with 5 questions. Each question has one correct answer.

Previous Next