SQL 57 🛢️ Tile Distribution with NTILE - KandZ

The NTILE function divides an ordered set of rows into a specified number of approximately equal groups, called tiles or buckets. It is the ranking function that answers “which quartile is this row in?” or “which decile?” or “which third?” The function assigns a bucket number to each row, starting at 1, and the buckets are as equal in size as the row count allows. It is the tool for the percentile analysis, the cohort segmentation, and the even distribution of the work across a fixed number of groups.

Key point: NTILE(n) divides the rows into n buckets. The buckets are numbered from 1 to n. The rows are divided as evenly as possible: if the row count is not divisible by n, the first buckets get one extra row. The ORDER BY inside the OVER clause determines the order of the rows and which rows land in which bucket. The PARTITION BY resets the division for each group.

Why NTILE exists

The percentile problem. A report needs the top quartile, the middle half, or the bottom decile. The NTILE(4) assigns the quartile number, the NTILE(10) the decile, and the filter on the bucket selects the group. The function is the tool for the percentile analysis.

The even-distribution problem. The rows should be divided into n groups of the approximately equal size. The NTILE divides the rows as evenly as possible, and the difference between the largest and the smallest bucket is at most one row. The even distribution is the guarantee.

The cohort problem. The customers should be divided into the n cohorts for the A/B testing. The NTILE(n) assigns the cohort number, and each cohort is the approximately equal size. The pattern is for the experiment design.

The load-balancing problem. The tasks should be divided among the n workers. The NTILE(n) assigns the worker number, and the workers get the approximately equal number of the tasks. The pattern is for the work distribution.

The bucket problem. The values should be grouped into the ranges of the equal frequency. The NTILE is the frequency-based grouping, and the CASE with the fixed thresholds is the value-based grouping. The NTILE is the tool when the frequency should be equal.

The ranking problem. The NTILE is the ranking function, and it shares the OVER clause with the ROW_NUMBER, the RANK, and the DENSE_RANK. The NTILE is the bucket, and the others are the ranks. The combination is the common in the analytical queries.

a. The basic NTILE

The NTILE(n) takes the number of the buckets as the argument.

SELECT
    employee_id,
    salary,
    NTILE(4) OVER (ORDER BY salary DESC) AS quartile
FROM employees;

The quartile is the bucket number from 1 to 4. The highest salaries are in the quartile 1, and the lowest are in the quartile 4. The rows are divided as evenly as possible.

The example with the 8 rows and the NTILE(4).

salary	quartile
100	1
95	1
90	2
85	2
80	3
75	3
70	4
65	4

The 8 rows divided into the 4 buckets of 2 rows each.

The example with the 10 rows and the NTILE(4).

salary	quartile
100	1
95	1
90	1
85	2
80	2
75	2
70	3
65	3
60	4
55	4

The 10 rows divided into the 4 buckets. The 10 / 4 = 2.5, so the first buckets get the extra rows: the quartile 1 gets the 3 rows, the quartile 2 gets the 3 rows, the quartile 3 gets the 2 rows, and the quartile 4 gets the 2 rows.

The rule: the first (row_count % n) buckets get (row_count / n) + 1 rows, and the rest get (row_count / n) rows. The % is the modulo.

The tiebreaker for the deterministic result.

SELECT
    employee_id,
    salary,
    NTILE(4) OVER (ORDER BY salary DESC, employee_id) AS quartile
FROM employees;

The employee_id is the tiebreaker. The tied salaries are ordered deterministically, and the bucket assignment is stable.

The NTILE with the partition.

SELECT
    employee_id,
    department,
    salary,
    NTILE(4) OVER (PARTITION BY department ORDER BY salary DESC) AS quartile
FROM employees;

The quartile resets for each department. The highest salary in each department gets the quartile 1, and the lowest gets the quartile 4.

b. The bucket size and the distribution

The bucket sizes are as equal as possible.

SELECT
    NTILE(4) OVER (ORDER BY salary DESC) AS quartile,
    COUNT(*) AS bucket_size
FROM employees
GROUP BY quartile
ORDER BY quartile;

The result shows the size of each bucket. The sizes differ by at most one.

The distribution example with the 11 rows and the NTILE(4).

row_count = 11
n = 4
base = 11 / 4 = 2
remainder = 11 % 4 = 3

bucket 1: 2 + 1 = 3 rows
bucket 2: 2 + 1 = 3 rows
bucket 3: 2 + 1 = 3 rows
bucket 4: 2 rows

The first 3 buckets get the 3 rows, and the last gets the 2. The total is the 11.

The distribution example with the 10 rows and the NTILE(3).

row_count = 10
n = 3
base = 10 / 3 = 3
remainder = 10 % 3 = 1

bucket 1: 3 + 1 = 4 rows
bucket 2: 3 rows
bucket 3: 3 rows

The first bucket gets the 4 rows, and the rest get the 3. The total is the 10.

The bucket size when the n is larger than the row count.

SELECT NTILE(10) OVER (ORDER BY salary DESC) AS bucket
FROM employees;
-- With 3 rows:
-- bucket 1: 1 row
-- bucket 2: 1 row
-- bucket 3: 1 row
-- buckets 4-10: empty

The first row_count buckets get the 1 row each, and the rest are the empty. The NTILE with the n larger than the row_count produces the empty buckets.

The distribution of the two values.

SELECT
    NTILE(4) OVER (ORDER BY salary DESC) AS quartile,
    MIN(salary) AS min_salary,
    MAX(salary) AS max_salary,
    AVG(salary) AS avg_salary
FROM employees
GROUP BY quartile
ORDER BY quartile;

The result shows the salary range and the average for each quartile. The pattern is for the quartile analysis.

c. The use cases

The NTILE has the distinct use cases.

Use Case	Function
The quartile	`NTILE(4)`
The decile	`NTILE(10)`
The percentile	`NTILE(100)`
The tercile	`NTILE(3)`
The cohort	`NTILE(n)`
The load balancing	`NTILE(n)`
The top X%	`NTILE(100)` + filter

The top 25% with the NTILE(4).

SELECT * FROM (
    SELECT
        employee_id,
        salary,
        NTILE(4) OVER (ORDER BY salary DESC) AS quartile
    FROM employees
) AS ranked
WHERE quartile = 1;

The result is the top quartile, the highest 25% of the salaries. The NTILE(4) with the filter is the pattern.

The top 10% with the NTILE(10).

SELECT * FROM (
    SELECT
        employee_id,
        salary,
        NTILE(10) OVER (ORDER BY salary DESC) AS decile
    FROM employees
) AS ranked
WHERE decile = 1;

The result is the top decile, the highest 10%. The NTILE(10) is the tool.

The bottom quartile.

SELECT * FROM (
    SELECT
        employee_id,
        salary,
        NTILE(4) OVER (ORDER BY salary DESC) AS quartile
    FROM employees
) AS ranked
WHERE quartile = 4;

The result is the bottom quartile, the lowest 25%. The NTILE(4) with the quartile = 4 is the pattern.

The cohort assignment.

SELECT
    user_id,
    NTILE(10) OVER (ORDER BY created_at) AS cohort
FROM users;

The users are divided into the 10 cohorts by the registration date. The NTILE(10) is the cohort number, and the pattern is for the A/B testing and the cohort analysis.

The load balancing.

SELECT
    task_id,
    NTILE(4) OVER (ORDER BY task_id) AS worker
FROM tasks;

The tasks are divided among the 4 workers. The NTILE(4) is the worker number, and the distribution is the approximately equal.

The percentile with the NTILE(100).

SELECT
    value,
    NTILE(100) OVER (ORDER BY value) AS percentile
FROM measurements;

The percentile is the bucket from 1 to 100. The percentile = 90 is the top 10%, and the percentile = 10 is the bottom 10%. The pattern is for the percentile analysis.

The comparison with the PERCENT_RANK and the CUME_DIST.

Function	Returns
`NTILE(n)`	The bucket 1 to n
`PERCENT_RANK()`	The relative rank 0 to 1
`CUME_DIST()`	The cumulative distribution 0 to 1
`ROW_NUMBER()`	The unique position

The NTILE is the discrete bucket, and the PERCENT_RANK and the CUME_DIST are the continuous values. The choice depends on the output.

Complete Example Session

-- ============================================
-- PART 1: SAMPLE DATA
-- ============================================
CREATE TABLE employees (
    employee_id INT,
    department VARCHAR(20),
    salary DECIMAL(10,2)
);

INSERT INTO employees VALUES
    (1, 'Eng', 100000),
    (2, 'Eng', 90000),
    (3, 'Eng', 80000),
    (4, 'Eng', 70000),
    (5, 'Eng', 60000),
    (6, 'Sales', 85000),
    (7, 'Sales', 75000),
    (8, 'Sales', 65000),
    (9, 'Sales', 55000),
    (10, 'Sales', 45000);

-- ============================================
-- PART 2: BASIC NTILE
-- ============================================
SELECT
    employee_id,
    salary,
    NTILE(4) OVER (ORDER BY salary DESC) AS quartile
FROM employees;
-- 10 rows, 4 buckets: sizes 3, 3, 2, 2

-- ============================================
-- PART 3: BUCKET SIZE
-- ============================================
SELECT
    NTILE(4) OVER (ORDER BY salary DESC) AS quartile,
    COUNT(*) AS bucket_size
FROM employees
GROUP BY quartile
ORDER BY quartile;

-- ============================================
-- PART 4: WITH PARTITION
-- ============================================
SELECT
    employee_id,
    department,
    salary,
    NTILE(4) OVER (PARTITION BY department ORDER BY salary DESC) AS quartile
FROM employees;

-- ============================================
-- PART 5: TOP QUARTILE
-- ============================================
SELECT * FROM (
    SELECT
        employee_id,
        salary,
        NTILE(4) OVER (ORDER BY salary DESC) AS quartile
    FROM employees
) AS ranked
WHERE quartile = 1;

-- ============================================
-- PART 6: BOTTOM QUARTILE
-- ============================================
SELECT * FROM (
    SELECT
        employee_id,
        salary,
        NTILE(4) OVER (ORDER BY salary DESC) AS quartile
    FROM employees
) AS ranked
WHERE quartile = 4;

-- ============================================
-- PART 7: DECILE
-- ============================================
SELECT
    employee_id,
    salary,
    NTILE(10) OVER (ORDER BY salary DESC) AS decile
FROM employees;

-- ============================================
-- PART 8: QUARTILE STATS
-- ============================================
SELECT
    NTILE(4) OVER (ORDER BY salary DESC) AS quartile,
    MIN(salary) AS min_salary,
    MAX(salary) AS max_salary,
    AVG(salary) AS avg_salary
FROM employees
GROUP BY quartile
ORDER BY quartile;

-- ============================================
-- PART 9: COHORT ASSIGNMENT
-- ============================================
SELECT
    user_id,
    NTILE(10) OVER (ORDER BY created_at) AS cohort
FROM users;

-- ============================================
-- PART 10: LOAD BALANCING
-- ============================================
SELECT
    task_id,
    NTILE(4) OVER (ORDER BY task_id) AS worker
FROM tasks;

The ten parts covered the sample data, the basic NTILE, the bucket size, the partition, the top quartile, the bottom quartile, the decile, the quartile stats, the cohort assignment, and the load balancing.

Quick Reference

NTILE Syntax

Form	Behavior
`NTILE(n) OVER (ORDER BY col)`	n buckets
`NTILE(n) OVER (PARTITION BY g ORDER BY col)`	n buckets per group

Bucket Distribution

row_count	n	Buckets
8	4	2, 2, 2, 2
10	4	3, 3, 2, 2
10	3	4, 3, 3
11	4	3, 3, 3, 2
3	10	1, 1, 1, 0, …

Common Buckets

n	Name
2	The median split
3	The tercile
4	The quartile
10	The decile
100	The percentile

Use Cases

Use Case	Pattern
Top 25%	`NTILE(4)` + `= 1`
Bottom 10%	`NTILE(10)` + `= 10`
Cohort	`NTILE(n)` by the date
Load balancing	`NTILE(n)` by the id
Quartile analysis	`NTILE(4)` + the stats

Best Practices

✅ Do This:

-- Use NTILE for the even buckets
NTILE(4) OVER (ORDER BY salary DESC)                           -- ✅

-- Add the tiebreaker for the deterministic result
NTILE(4) OVER (ORDER BY salary DESC, employee_id)              -- ✅

-- Use the partition for the per-group buckets
NTILE(4) OVER (PARTITION BY department ORDER BY salary DESC)   -- ✅

-- Wrap in the subquery to filter on the bucket
SELECT * FROM (
    SELECT *, NTILE(4) OVER (ORDER BY salary DESC) AS q FROM t
) AS ranked WHERE q = 1;                                       -- ✅

-- Use the bucket for the stats
SELECT NTILE(4) OVER (...) AS q, AVG(salary) FROM t GROUP BY q;-- ✅

-- Use the decile for the finer analysis
NTILE(10) OVER (ORDER BY score)                                -- ✅

❌ Don’t Do This:

-- Don't use the window function in WHERE
WHERE NTILE(4) OVER (...) = 1  -- error                         -- ❌

-- Don't forget the ORDER BY
NTILE(4) OVER ()  -- arbitrary, no meaning                      -- ❌

-- Don't expect the exactly equal buckets
NTILE(4) OVER (...)  -- 10 rows: 3, 3, 2, 2, not 2.5 each     -- ⚠️

-- Don't use NTILE for the value-based ranges
NTILE(4)  -- the frequency-based, not the fixed thresholds     -- ⚠️

-- Don't use NTILE when the ROW_NUMBER fits
NTILE(100)  -- for the top 1%? use the ROW_NUMBER + the math   -- ⚠️

-- Don't forget the partition when the per-group is needed
NTILE(4) OVER (ORDER BY salary)  -- no partition               -- ⚠️

Common Pitfalls

Pitfall	Why It Happens	Fix
Window function in `WHERE`	Not allowed	Wrap in the subquery
Uneven buckets	The integer division	Expected, `n` divides
Empty buckets	`n` > row count	Check the `n`
Non-deterministic	No tiebreaker	Add the column
Wrong per-group	No partition	Add the `PARTITION BY`
Value ranges	The `NTILE` is frequency	Use the `CASE`

Real-World Examples

1. Top Quartile

SELECT * FROM (
    SELECT *, NTILE(4) OVER (ORDER BY salary DESC) AS q FROM employees
) AS ranked WHERE q = 1;

2. Bottom Decile

SELECT * FROM (
    SELECT *, NTILE(10) OVER (ORDER BY score) AS d FROM scores
) AS ranked WHERE d = 1;

3. Quartile Stats

SELECT NTILE(4) OVER (ORDER BY salary DESC) AS q, AVG(salary)
FROM employees GROUP BY q;

4. Cohort Assignment

SELECT user_id, NTILE(10) OVER (ORDER BY created_at) AS cohort
FROM users;

5. Load Balancing

SELECT task_id, NTILE(4) OVER (ORDER BY task_id) AS worker
FROM tasks;

6. Per-Department Quartile

SELECT *, NTILE(4) OVER (PARTITION BY dept ORDER BY salary DESC) AS q
FROM employees;

7. Percentile Bucket

SELECT value, NTILE(100) OVER (ORDER BY value) AS pctile
FROM measurements;

8. Top 10%

SELECT * FROM (
    SELECT *, NTILE(10) OVER (ORDER BY revenue DESC) AS d FROM sales
) AS ranked WHERE d = 1;

9. Tercile

SELECT *, NTILE(3) OVER (ORDER BY score) AS tercile FROM results;

10. Median Split

SELECT *, NTILE(2) OVER (ORDER BY age) AS half FROM people;

Visual

NTILE Distribution

┌─────────────────────────────────────────────────────────────┐
│  NTILE(4) OVER (ORDER BY salary DESC)                       │
│                                                             │
│  10 rows, 4 buckets:                                        │
│                                                             │
│  ┌────────┬──────────┐                                      │
│  │ salary │ quartile │                                      │
│  ├────────┼──────────┤                                      │
│  │ 100    │ 1        │                                      │
│  │ 95     │ 1        │  bucket 1: 3 rows                    │
│  │ 90     │ 1        │                                      │
│  │ 85     │ 2        │                                      │
│  │ 80     │ 2        │  bucket 2: 3 rows                    │
│  │ 75     │ 2        │                                      │
│  │ 70     │ 3        │                                      │
│  │ 65     │ 3        │  bucket 3: 2 rows                    │
│  │ 60     │ 4        │                                      │
│  │ 55     │ 4        │  bucket 4: 2 rows                    │
│  └────────┴──────────┘                                      │
│                                                             │
│  The first buckets get the extra rows.                      │
│                                                             │
└─────────────────────────────────────────────────────────────┘

Bucket Size Rule

┌─────────────────────────────────────────────────────────────┐
│  row_count = 10, n = 4                                      │
│                                                             │
│  base = 10 / 4 = 2                                          │
│  remainder = 10 % 4 = 2                                     │
│                                                             │
│  bucket 1: 2 + 1 = 3 rows                                   │
│  bucket 2: 2 + 1 = 3 rows                                   │
│  bucket 3: 2 rows                                           │
│  bucket 4: 2 rows                                           │
│                                                             │
│  The first 2 buckets get the extra rows.                    │
│  The difference between the largest and smallest is 1.      │
│                                                             │
└─────────────────────────────────────────────────────────────┘

NTILE vs ROW_NUMBER

┌─────────────────────────────────────────────────────────────┐
│  ROW_NUMBER() OVER (ORDER BY salary DESC)                   │
│                                                             │
│  ┌────────┬────┐                                            │
│  │ salary │ rn │                                            │
│  ├────────┼────┤                                            │
│  │ 100    │ 1  │  ← the unique position                     │
│  │ 95     │ 2  │                                            │
│  │ 90     │ 3  │                                            │
│  │ 85     │ 4  │                                            │
│  └────────┴────┘                                            │
│                                                             │
├─────────────────────────────────────────────────────────────┤
│                                                             │
│  NTILE(4) OVER (ORDER BY salary DESC)                       │
│                                                             │
│  ┌────────┬──────────┐                                      │
│  │ salary │ quartile │                                      │
│  ├────────┼──────────┤                                      │
│  │ 100    │ 1        │  ← the bucket number                 │
│  │ 95     │ 1        │                                      │
│  │ 90     │ 1        │                                      │
│  │ 85     │ 2        │                                      │
│  └────────┴──────────┘                                      │
│                                                             │
│  The ROW_NUMBER is the position.                            │
│  The NTILE is the bucket.                                   │
│                                                             │
└─────────────────────────────────────────────────────────────┘

Percentile Analysis

┌─────────────────────────────────────────────────────────────┐
│  NTILE(100) OVER (ORDER BY value) AS percentile             │
│                                                             │
│  percentile = 1   → the bottom 1%                           │
│  percentile = 10  → the bottom 10%                          │
│  percentile = 50  → the median                              │
│  percentile = 90  → the top 10%                             │
│  percentile = 100 → the top 1%                              │
│                                                             │
│  The NTILE(100) is the percentile bucket.                   │
│  The filter on the bucket is the percentile group.          │
│                                                             │
└─────────────────────────────────────────────────────────────┘

Summary

Item	Value
Function	`NTILE(n)`
Buckets	1 to n
Distribution	As even as possible
Extra rows	The first buckets
`ORDER BY`	Required
`PARTITION BY`	Optional, resets
Filter	Subquery + outer `WHERE`
Common	`NTILE(4)`, `NTILE(10)`, `NTILE(100)`
Use	Quartile, decile, percentile, cohort

Key takeaways:

The NTILE(n) divides the rows into the n buckets. The buckets are numbered from 1 to n. The rows are divided as evenly as possible, and the first buckets get the extra rows when the row count is not divisible by n.
The ORDER BY determines the order. The rows are sorted by the ORDER BY inside the OVER clause, and the sorted order determines which rows land in which bucket. The ORDER BY is required.
The PARTITION BY resets the division. The NTILE(4) OVER (PARTITION BY department ORDER BY salary DESC) assigns the quartile within each department, and the numbering resets for each group.
The bucket sizes differ by at most one. The NTILE(4) on the 10 rows produces the buckets of 3, 3, 2, 2. The guarantee is the even distribution, and the difference between the largest and the smallest is at most 1.
The NTILE cannot be used in the WHERE. The window function is evaluated after the WHERE. The filter on the bucket requires the subquery: the inner query computes the bucket, and the outer query filters.
The NTILE is the bucket, and the ROW_NUMBER is the position. The NTILE is the discrete bucket for the percentile analysis. The ROW_NUMBER is the unique position for the pagination and the deduplication.
The use cases are the quartile, the decile, the percentile, the cohort, and the load balancing. The NTILE(4) for the quartile, the NTILE(10) for the decile, the NTILE(100) for the percentile, and the NTILE(n) for the cohort and the load balancing.

Remember: The NTILE is the tool for the even buckets. It divides the ordered rows into the n groups of the approximately equal size, and the bucket number is the group label. The ORDER BY determines the order, and the PARTITION BY resets the division. The filter on the bucket is the percentile group, and the subquery is the mechanism. The NTILE is the discrete bucket, and the PERCENT_RANK and the CUME_DIST are the continuous values. The choice depends on the output and the analysis.

Stop using slow, ad-bloated tool sites! 🤮

🔎 Search “KandZ Tools” on Google to use many professional utilities for free.

KandZ.me is the ultimate minimalist hub for:
✅ Finance (Mortgage, Interest, Inflation)
✅ Tech (Base64, JSON, Dev Suite, IP)
✅ Health (BMI, BMR, TDEE)
✅ Productivity (Timer, Workspace, QR)

⚡️ Fast & Private
🔒 No data leaves your device
💎 100% Free

🔗 Use it now: https://tools.kandz.me
🔖 Bookmark it—you’ll need it later!