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.
Lesson 03
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.
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:
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.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:
| Function | Formula | Lowest row | Highest row |
|---|---|---|---|
NTILE(n) | Bucket 1 to n, sizes differ by at most 1 | 1 | n |
PERCENT_RANK() | (rank - 1) / (N - 1) | always 0 | 1 (if unique) |
CUME_DIST() | rows at or before this one / N | 1 / N (if unique) | always 1 |
As with every ranking function, the ORDER BY inside OVER is what gives these numbers meaning.
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;
| quartile | employees | lowest | highest |
|---|---|---|---|
| 1 | 10 | 2500.00 | 4800.00 |
| 2 | 10 | 4800.00 | 7900.00 |
| 3 | 10 | 8000.00 | 10500.00 |
| 4 | 10 | 11000.00 | 24000.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.
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_tier | customers | min_spent | max_spent |
|---|---|---|---|
| 1 | 7 | 354.86 | 586.00 |
| 2 | 7 | 254.98 | 349.95 |
| 3 | 6 | 59.98 | 169.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.
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_name | appointments | pct_rank | cume |
|---|---|---|---|
| Johnson | 8 | 0.79 | 1.00 |
| Bennett | 7 | 0.71 | 0.73 |
| Carter | 5 | 0.64 | 0.67 |
| Dubois | 1 | 0.07 | 0.13 |
| Wright | 0 | 0.00 | 0.07 |
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.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?"
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.
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.
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).
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.
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.
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.
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.
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.PERCENT_RANK and CUME_DIST to numeric before ROUND(x, n).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.
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.
4, 3 and 3. The extra row goes to the first bucket.
Concept check
3 quick questions. One at a time. Instant score at the end.
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.
No results yet
Run a query to see results here
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.
No results yet
Run a query to see results here
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.
No results yet
Run a query to see results here