Course contentsShow
← Data Science
Lesson 1010 of 2,14526. Advanced SQLFree lesson

NTILE(): Dividing Rows into Buckets

Using NTILE(n) to divide ordered rows into n approximately equal groups for quartiles, deciles, or percentiles.

NTILE(): Dividing Rows into Buckets

You'll learn how to use NTILE() to split ordered data into equal-sized groups for percentile analysis.

What is NTILE()?

NTILE(n) is a window function that divides your result set into n approximately equal buckets (or "tiles"), assigning each row a bucket number from 1 to n. Think of it like sorting students by test scores and then dividing them into quartiles (top 25%, second 25%, etc.) — NTILE() automates this grouping.

Unlike ROW_NUMBER(), RANK(), or DENSE_RANK() which assign individual positions, NTILE() assigns group membership.

Basic Syntax

NTILE(n) OVER (ORDER BY column)

Where n is the number of buckets you want to create.

Common Use Cases

Quartiles (4 groups):

SELECT 
    employee_name,
    salary,
    NTILE(4) OVER (ORDER BY salary DESC) AS salary_quartile
FROM employees;

This assigns 1 to the top 25% earners, 2 to the next 25%, and so on.

Deciles (10 groups) or Percentiles (100 groups):

NTILE(10) OVER (ORDER BY sales_amount DESC) AS decile
NTILE(100) OVER (ORDER BY revenue) AS percentile

How NTILE Distributes Rows

If rows don't divide evenly, the earlier buckets get one extra row each. For example, 23 rows into 4 buckets creates: 6, 6, 6, 5 rows respectively.

With PARTITION BY

NTILE(3) OVER (PARTITION BY department ORDER BY salary DESC)

This creates 3 buckets within each department separately.

Key Takeaway: NTILE(n) divides ordered rows into n approximately equal-sized buckets, making it perfect for percentile analysis, quartile segmentation, and distributing data into balanced groups.