SQLearnAcademy
←Function reference
WindowSQL standard

CUME_DIST

Shows the share of rows that come before the current one or tie with it — from 0 to 1.

Syntax

CUME_DIST() OVER (ORDER BY <sort key>)
CUME_DIST() OVER (PARTITION BY <group> ORDER BY <sort key>)

Calculates (rows up to the current one, ties included) / (all rows). Unlike PERCENT_RANK, it never gives 0: the first row gets at least 1 / count, and the last always gets 1. Handy for phrases like “80% of people earn the same or less”.

Data for the examples

employees

namedepartmentsalarybonusphone
Emmabackend1200300+44 7700 900123
Liambackend1000NULLNULL
Oliviafrontend1350150+44 7700 900456
Noahfrontend950NULL+44 7700 900789
Sophiadesign1200200NULL

These tables already exist in the sandbox — the examples below use them. Want to try this in your own database? Copy the SQL.

The share by salary
SELECT name, salary,
CUME_DIST() OVER (ORDER BY salary) AS cume_dist
FROM employees;
namesalarycume_dist
Noah9500.2
Liam10000.4
Emma12000.8
Sophia12000.8
Olivia13501
In percent
SELECT name, salary,
ROUND(CUME_DIST() OVER (ORDER BY salary) * 100) AS at_most_percent
FROM employees;
namesalaryat_most_percent
Noah95020
Liam100040
Emma120080
Sophia120080
Olivia1350100

80% of people earn the same as Emma or less.

PERCENT_RANK and CUME_DIST side by side
SELECT name, salary,
PERCENT_RANK() OVER (ORDER BY salary) AS percent_rank,
CUME_DIST() OVER (ORDER BY salary) AS cume_dist
FROM employees;
namesalarypercent_rankcume_dist
Noah95000.2
Liam10000.250.4
Emma12000.50.8
Sophia12000.50.8
Olivia135011

Watch out

Equal values get the same result and are counted together: Emma and Sophia both have 0.8, because four out of five earn 1200 or less.

There's no zero, even for the smallest value. If the first row should have 0, use PERCENT_RANK.

The result is a fraction, not a percentage: multiply by 100 for percent.