Skip to content
Tutorials Center
My learning

Topics

SQL Reference ยท Window Functions

SQL PERCENT_RANK() and CUME_DIST()

Reference for SQL PERCENT_RANK() and CUME_DIST(): relative rank and cumulative distribution as fractions between 0 and 1, formulas, ties and percentile use.

PERCENT_RANK() and CUME_DIST() express a row's position in its window as a fraction. They answer questions such as "what share of employees earn less than this person" without you having to count rows yourself. Both are ordered by the window's ORDER BY and restart in every partition.

They differ in the question they answer. PERCENT_RANK() measures how far a row is along the ranking, from 0 for the first rank up to 1 for the last. CUME_DIST() measures the share of rows that sort at or before the current row, so it is never 0 and the last row is always 1.

SQL
SELECT first_name, salary,
       PERCENT_RANK() OVER (ORDER BY salary) AS pct_rank,
       CUME_DIST()    OVER (ORDER BY salary) AS cume_dist
FROM employees;

Try it

SQL Both functions on ten grades with a tie ยท database: school

Press Run (or Ctrl + Enter) to see the result here.

SQL Employees in the top 20 percent of pay ยท database: company

Press Run (or Ctrl + Enter) to see the result here.

Syntax

SQL
PERCENT_RANK() OVER ([PARTITION BY ...] ORDER BY expression [ASC | DESC] [, ...])
CUME_DIST()    OVER ([PARTITION BY ...] ORDER BY expression [ASC | DESC] [, ...])

Let N be the number of rows in the partition, rank the value RANK() would give, and peers the number of rows that sort at or before the current row (including all ties):

Function Formula Range
PERCENT_RANK() (rank - 1) / (N - 1) 0 to 1 (0 for a single-row partition)
CUME_DIST() peers / N greater than 0, up to 1

Returns: a floating-point number (double precision, or NUMBER in Oracle). Neither function takes arguments.

Dialect differences

Feature SQLite PostgreSQL MySQL SQL Server Oracle
PERCENT_RANK(), CUME_DIST() windowed 3.25+ 8.4+ 8.0+ 2012+ Yes
Hypothetical-set form (WITHIN GROUP) No Yes No No Yes
PERCENTILE_CONT / PERCENTILE_DISC No Yes (ordered-set) No Yes (as window function) Yes
SQL
-- PostgreSQL, Oracle: fraction of salaries at or below 90000 without storing a row
SELECT CUME_DIST(90000) WITHIN GROUP (ORDER BY salary) FROM employees;

-- PostgreSQL: the median salary
SELECT percentile_cont(0.5) WITHIN GROUP (ORDER BY salary) FROM employees;

Usage notes

  • PERCENT_RANK() is based on RANK(), so tied rows share a value and the next distinct value jumps ahead. It starts at exactly 0 and ends at exactly 1 (for two or more rows).
  • CUME_DIST() counts ties together. All peers share the same value, which is the share of rows up to and including the last peer.
  • For "top X percent" filters, CUME_DIST() is simple: with ORDER BY salary, cume_dist > 0.8 keeps the highest paid fifth or a bit more, depending on ties.
  • These are not percentages. Multiply by 100 or format them for display.
  • The sort direction decides everything. Order ascending and a high value means "high in the list"; order descending and it means "near the top".
  • Single-row partitions: PERCENT_RANK() returns 0 to avoid dividing by zero; CUME_DIST() returns 1.
  • Floating-point output: round it for display, and do not compare to a decimal literal with =.
  • When you need buckets, use NTILE() instead; for a real median use the percentile functions where they exist.