Bucket Rows Into Equal Groups With NTILE

Use `NTILE(n)` to divide an ordered result set into `n` roughly equal buckets and assign each row a bucket number.

5/5 supported engines validation-green 1 example 2 scenarios

Docker-validated Not currently validation-green

Example 1

Assign eight students to four quartiles by test score

With 8 rows and NTILE(4), each bucket gets exactly 2 students. Bucket 1 contains the top two scores (Alice 95, Eve 91), bucket 2 the next two (Grace 88, Bob 82), and so on down to bucket 4 (Dave 65, Hank 55). If there were 9 students, the first bucket would receive 3 rows and the remaining three buckets 2 rows each.

Source table data Rows loaded before the example query runs.

Setup

CREATE TABLE test_scores (student VARCHAR(50), score INT);

INSERT INTO
  test_scores
VALUES
  ('Alice', 95),
  ('Bob', 82),
  ('Carol', 78),
  ('Dave', 65),
  ('Eve', 91),
  ('Frank', 74),
  ('Grace', 88),
  ('Hank', 55);

Validated query Shared across supported engines.

SQL

SELECT
  student,
  score,
  NTILE (4) OVER (
    ORDER BY
      score DESC
  ) AS quartile
FROM
  test_scores
ORDER BY
  score DESC;

Expected result Returned rows for the shared example.

student	score	quartile
Alice	95	1
Eve	91	1
Grace	88	2
Bob	82	2
Carol	78	3
Frank	74	3
Dave	65	4
Hank	55	4

The NTILE syntax is identical across all supported engines.

Useful when

Where this command helps.

dividing customers into four equal quartiles by lifetime spend for targeted marketing
labelling the top 10% of scores for further analysis without hard-coding a threshold

Explanation

What the command is doing.

NTILE(n) OVER (ORDER BY col) distributes rows into n numbered groups as evenly as possible. If the total number of rows does not divide evenly into n, the earlier buckets receive one extra row. The function assigns a bucket number — not a rank or row number — so all rows in the same bucket share the same number. Common uses include quartiles (NTILE(4)), deciles (NTILE(10)), and percentile bands for reporting and machine-learning feature engineering.

Bucket Rows Into Equal Groups With NTILE

Assign eight students to four quartiles by test score

Where this command helps.

What the command is doing.

Keep moving through nearby tasks.