NTILE and PERCENT_RANK — statistical ranking

These window functions assign relative positions to rows within an ordered set.

NTILE(n) — divide rows into n equal buckets

Assigns each row a bucket number from 1 to n. Useful for quartiles, deciles, percentiles.

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

• Quartile 1 = lowest salaries, 4 = highest.
• If rows don't divide evenly, earlier buckets get one extra row.
• Partition to rank within a group: NTILE(4) OVER (PARTITION BY department_id ORDER BY salary).

PERCENT_RANK() — relative rank as 0.0 → 1.0

PERCENT_RANK = (rank - 1) / (total rows - 1)

SELECT
  first_name, salary,
  ROUND(PERCENT_RANK() OVER (ORDER BY salary) * 100, 1) AS pct_rank
FROM employees;

A value of 0.90 means the employee earns more than 90% of colleagues.

CUME_DIST() — cumulative distribution

Similar to PERCENT_RANK but counts the current row in the numerator:

SELECT
  first_name, salary,
  ROUND(CUME_DIST() OVER (ORDER BY salary) * 100, 1) AS cume_pct
FROM employees;

CUME_DIST is always > 0; PERCENT_RANK of the lowest row is always 0.

Practical pattern: find top quartile

WITH quartiled AS (
  SELECT first_name, salary,
    NTILE(4) OVER (ORDER BY salary ASC) AS quartile
  FROM employees
)
SELECT first_name, salary
FROM quartiled
WHERE quartile = 4
ORDER BY salary DESC;

Tips

• Use NTILE for segmentation (top/middle/bottom); use PERCENT_RANK for exact relative positioning.
• NTILE requires an ORDER BY inside OVER().
• For business reporting: NTILE(100) gives percentiles (1–100).