Skip to main content
Window Functions
3 / 4
0%

NTILE and PERCENT_RANK — statistical ranking

0/3 solved3 to unlock next
PrevLocked

Lesson 03

NTILE and PERCENT_RANK — statistical ranking

Lesson Description

Segment distributions with NTILE and measure relative position with PERCENT_RANK. This lesson is ideal for percentile analysis, performance banding, and comparative reporting.

Easy Project

Mini project: Build a compensation ranking dashboard query with department-level rank and delta from the top earner.

From "what rank?" to "what share?"

RANK() from the first lesson tells you that an employee is 31st of 40 by salary. That is precise, but it is hard to compare across groups of different sizes. Being 3rd in a team of 5 and 3rd in a team of 50 are very different achievements.

Analysts usually want relative positions instead:

  • "Split our store customers into high, middle and low spenders so marketing can target each group."
  • "Which salary quartile does each employee fall into?"
  • "What percentage of doctors handle fewer appointments than Dr. Bennett?"

PostgreSQL gives you three window functions for this:

  • NTILE(n) divides the ordered rows into n buckets of (nearly) equal size and tells you which bucket each row is in.
  • PERCENT_RANK() expresses a row's rank as a fraction between 0 and 1.
  • CUME_DIST() gives the fraction of rows that sort at or before the current row.

The syntax

NTILE(n)       OVER ([PARTITION BY ...] ORDER BY ...)
PERCENT_RANK() OVER ([PARTITION BY ...] ORDER BY ...)
CUME_DIST()    OVER ([PARTITION BY ...] ORDER BY ...)

The formulas, where rank is what RANK() would return and N is the number of rows in the partition:

FunctionFormulaLowest rowHighest row
NTILE(n)Bucket 1 to n, sizes differ by at most 11n
PERCENT_RANK()(rank - 1) / (N - 1)always 01 (if unique)
CUME_DIST()rows at or before this one / N1 / N (if unique)always 1

As with every ranking function, the ORDER BY inside OVER is what gives these numbers meaning.

Example 1: salary quartiles with NTILE

Split the 40 HR employees into four salary bands, then summarize each band:

WITH banded AS (
  SELECT salary,
         NTILE(4) OVER (ORDER BY salary) AS quartile
  FROM employees
)
SELECT quartile, COUNT(*) AS employees,
       MIN(salary) AS lowest, MAX(salary) AS highest
FROM banded
GROUP BY quartile
ORDER BY quartile;
quartileemployeeslowesthighest
1102500.004800.00
2104800.007900.00
3108000.0010500.00
41011000.0024000.00

Forty rows divide evenly by four, so each bucket holds exactly 10. Quartile 1 is the lowest-paid group because the window orders by salary ascending.

Look at the boundary between quartiles 1 and 2: both contain 4,800. That is not a typo. Valli Pataballa and David Austin both earn 4,800, and NTILE only cares about filling buckets to the right size. One of them lands in quartile 1 and the other in quartile 2. Keep that in mind; it comes back in the common mistakes section.

Example 2: uneven buckets for customer segments

Now a marketing question in the store sandbox. Rank customers by how much they have spent on non-cancelled orders and split them into three tiers:

WITH spend AS (
  SELECT c.id, c.first_name, c.last_name,
         SUM(oi.quantity * oi.unit_price) AS total_spent
  FROM customers c
  JOIN orders o       ON o.customer_id = c.id
  JOIN order_items oi ON oi.order_id = o.id
  WHERE o.status <> 'cancelled'
  GROUP BY c.id, c.first_name, c.last_name
),
tiered AS (
  SELECT *, NTILE(3) OVER (ORDER BY total_spent DESC) AS spend_tier
  FROM spend
)
SELECT spend_tier, COUNT(*) AS customers,
       MIN(total_spent) AS min_spent, MAX(total_spent) AS max_spent
FROM tiered
GROUP BY spend_tier
ORDER BY spend_tier;
spend_tiercustomersmin_spentmax_spent
17354.86586.00
27254.98349.95
3659.98169.95

Twenty customers have spending, and 20 does not divide by 3. PostgreSQL handles the remainder by giving the earlier buckets one extra row each, so you get 7, 7 and 6. Because the order is DESC, tier 1 is now the top spenders. The direction of ORDER BY decides whether bucket 1 is the best or the worst, so always state it explicitly.

Example 3: PERCENT_RANK and CUME_DIST side by side

PERCENT_RANK() and CUME_DIST() look similar, so compare them on the same data. In the hospital sandbox, measure each doctor's workload relative to the other 14:

SELECT d.last_name, d.specialty,
       COUNT(a.id) AS appointments,
       ROUND(PERCENT_RANK() OVER (ORDER BY COUNT(a.id))::numeric, 2) AS pct_rank,
       ROUND(CUME_DIST()    OVER (ORDER BY COUNT(a.id))::numeric, 2) AS cume
FROM doctors d
LEFT JOIN appointments a ON a.doctor_id = d.id
GROUP BY d.id, d.last_name, d.specialty
ORDER BY appointments DESC, d.last_name;

The result has 15 rows. A few of them tell the story:

last_nameappointmentspct_rankcume
Johnson80.791.00
Bennett70.710.73
Carter50.640.67
Dubois10.070.13
Wright00.000.07
  • Marcus Wright has 0 appointments, the minimum, so his PERCENT_RANK is exactly 0. His CUME_DIST is 1/15, about 0.07, because he is the only row at or below his value.
  • Four doctors (Johnson, Khan, Patel and Tanaka) tie at 8. They all share RANK() 12, so PERCENT_RANK is (12 - 1) / 14 ≈ 0.79. It never reaches 1 here because nobody is uniquely at the top.
  • CUME_DIST for those four is 1.00 because all 15 rows are at or below 8.

A useful way to read them: PERCENT_RANK answers "what fraction of the others are below me?", while CUME_DIST answers "what fraction of everyone, including me and my ties, is at or below me?"

Example 4: relative position inside each department

Add PARTITION BY to compare people only with their own colleagues:

SELECT first_name, last_name, department_id, salary,
       ROUND(PERCENT_RANK() OVER (
         PARTITION BY department_id ORDER BY salary
       )::numeric, 2) AS dept_pct
FROM employees
WHERE department_id IN (30, 80)
ORDER BY department_id, salary;

This returns 15 rows: 6 in Purchasing and 9 in Sales. In Purchasing, the steps are 0.20 apart (5 gaps between 6 people), and Den Raphaely tops out at 1.00. In Sales they are 0.125 apart, so Gerald Cambrault at 11,000 sits at 0.63 in his department. Den Raphaely earns the same 11,000 but is at 1.00 in his. Same salary, very different standing, which is exactly the insight a plain company-wide ranking would hide.

How PostgreSQL evaluates it

These functions run in the same step as every other window function: after WHERE, GROUP BY and HAVING, before the final ORDER BY and LIMIT. So Example 2 and Example 3 can bucket aggregated values, and you still need a CTE or subquery to filter on the result.

Two type details matter in PostgreSQL:

  • NTILE returns an integer.
  • PERCENT_RANK and CUME_DIST return double precision. The two-argument ROUND(value, places) only exists for numeric, which is why the examples cast with ::numeric before rounding.

Ties are handled differently by each function. PERCENT_RANK and CUME_DIST are based on peer groups, so tied rows always get identical values. NTILE is based on row positions, so tied rows can be split across buckets.

Common mistakes

Rounding a double without a cast

SELECT first_name,
       ROUND(PERCENT_RANK() OVER (ORDER BY salary) * 100, 1) AS pct_rank
FROM employees;
ERROR:  function round(double precision, integer) does not exist
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.

Fix it with a cast: ROUND((PERCENT_RANK() OVER (ORDER BY salary) * 100)::numeric, 1).

Expecting NTILE to keep ties together

If you define "bottom quartile" as NTILE(4) ... = 1, you get exactly 10 employees, and only one of the two 4,800 earners is included. If you define it as CUME_DIST() <= 0.25, you get 9 employees, because both 4,800 earners sit at 0.275 and are excluded together. Neither is wrong, but they answer different questions. Use NTILE when equal-sized groups matter more than fairness at the boundary; use PERCENT_RANK or CUME_DIST thresholds when equal values must be treated equally.

Asking for more buckets than rows

NTILE(100) on 40 employees does not produce percentiles. It produces buckets 1 through 40, one row each, and buckets 41 to 100 are simply never used. For real percentile values, use PERCENT_RANK, or the aggregate percentile_cont(0.9) WITHIN GROUP (ORDER BY salary) if you want the salary at the 90th percentile.

Other databases

NTILE, PERCENT_RANK and CUME_DIST are part of standard SQL and are available in SQL Server 2012+, Oracle, MySQL 8.0+ and SQLite 3.25+, with the same tie behavior. The rounding issue is PostgreSQL-specific: MySQL and SQL Server let you ROUND a floating point value directly.

Practice

The three exercises below all use the HR sandbox. The first asks you to put every employee in a salary quartile. The second asks for each employee's PERCENT_RANK as a percentage, so remember the cast from this lesson. The third asks you to list only the employees in the top quartile, which means wrapping the bucket calculation and filtering outside it. Watch the sort direction in each prompt.

Key takeaways

  • NTILE(n) splits ordered rows into n buckets whose sizes differ by at most one, with earlier buckets taking the extra rows.
  • PERCENT_RANK() is (rank - 1) / (N - 1): 0 for the lowest row, and ties share a value.
  • CUME_DIST() is the fraction of rows at or below the current one: never 0, and 1 for the highest.
  • NTILE can split tied values across buckets; percentage functions never do.
  • In PostgreSQL, cast PERCENT_RANK and CUME_DIST to numeric before ROUND(x, n).

NTILE and PERCENT_RANK — statistical ranking: in depth

Distribution functions place each row relative to the rest: which salary quartile someone is in, what percentile a customer's spend reaches. They are the basis for segmentation, banding and "top 10%" analyses.

Worked examples

Salary quartiles

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

Splits employees into four groups of near-equal size. Quartile 4 holds the highest salaries.

Percentile of each customer's spend

WITH spend AS (
  SELECT o.customer_id, SUM(oi.quantity * oi.unit_price) AS total
  FROM orders o
  JOIN order_items oi ON oi.order_id = o.id
  GROUP BY o.customer_id
)
SELECT customer_id, total,
       ROUND(PERCENT_RANK() OVER (ORDER BY total)::numeric, 2) AS pct_rank,
       ROUND(CUME_DIST()    OVER (ORDER BY total)::numeric, 2) AS cume_dist
FROM spend
ORDER BY total DESC;

PERCENT_RANK runs from 0 to 1; CUME_DIST is the share of customers spending the same or less.

Common mistakes

  • Expecting NTILE to respect ties. Equal values can land in different buckets because NTILE splits by row count.
  • Confusing PERCENT_RANK with CUME_DIST. The lowest value always has PERCENT_RANK 0, while its CUME_DIST is greater than 0.
  • Using NTILE on very few rows; with 5 rows and NTILE(4), one bucket has two rows and the others one.

Other databases

  • NTILE, PERCENT_RANK and CUME_DIST are standard window functions in PostgreSQL, SQL Server, Oracle, SQLite 3.25+ and MySQL 8.0+.
  • For an exact median or percentile value, PostgreSQL uses PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY salary).
Check yourself: With 10 employees, how many land in each group of NTILE(3)?

4, 3 and 3. The extra row goes to the first bucket.

Concept check

Quick check: NTILE and PERCENT_RANK — statistical ranking

3 quick questions. One at a time. Instant score at the end.

Exercise 1

Assign employees to salary quartiles

Enterprise Workforce AnalyticseasyPostgreSQL

Return first_name, last_name, salary, and quartile (1–4) using NTILE(4) ordered by salary ascending. Quartile 1 = lowest earners. Order by quartile, then salary.

Autocomplete: start typing table/column namesPaste supported

No results yet

Run a query to see results here

Exercise 2

Salary percentile rank

Enterprise Workforce AnalyticseasyPostgreSQL

Return first_name, last_name, salary, and pct_rank — the PERCENT_RANK() (multiplied by 100 and rounded to 2 decimal places) of each employee ordered by salary. Order by salary.

Autocomplete: start typing table/column namesPaste supported

No results yet

Run a query to see results here

Exercise 3

Employees in the top salary quartile

Enterprise Workforce AnalyticseasyPostgreSQL

Using a CTE with NTILE(4), return only the employees in the top salary quartile (quartile = 4). Show first_name, last_name, and salary, ordered by salary descending.

Autocomplete: start typing table/column namesPaste supported

No results yet

Run a query to see results here

Window Functions
3 / 4
0%

NTILE and PERCENT_RANK — statistical ranking

0/3 solved3 to unlock next
PrevLocked