CUME_DIST()
SQL · Window Functions
Theory
CUME_DIST() is the cumulative distribution: the fraction of rows in the partition whose ORDER BY value is less than or equal to the current row's value. The formula is (number of rows with value <= this value) / (rows in partition). The result is always greater than 0 and at most 1, and the last value always gets exactly 1.
For salaries 45000, 52000, 60000, 60000, 75000 the results are 0.2, 0.4, 0.8, 0.8 and 1. Both 60000 rows get 0.8 because four of the five rows are at or below 60000; ties count all their peers. Compare that with PERCENT_RANK on the same data (0, 0.25, 0.5, 0.5, 1): PERCENT_RANK asks 'what share of the others are strictly below me', while CUME_DIST asks 'what share of everyone is at or below me'. A value of 0.8 reads naturally as 'this salary is at the 80th percentile'.
Why we use it
Questions like 'what fraction of orders are under ₹2000' or 'is this salary in the top 20%' are about distribution. CUME_DIST gives that share directly on every row, so you can filter or label rows by percentile.
When to use it
Use CUME_DIST for 'at or below' percentile statements and to keep tied values together. Use PERCENT_RANK for a 0-based relative rank, and NTILE when you need fixed equal-sized buckets.
How it works
- Rows sorted by the window ORDER BY
- Count rows in the partition (N)
- Count rows with value <= current value, ties included
- Divide that count by N
- Value in (0, 1] for each row; the last value gets 1
Syntax
CUME_DIST() OVER (ORDER BY col)Example
SELECT name, salary,
CUME_DIST() OVER (ORDER BY salary) AS cume_dist
FROM employees;Key points
- Always returns a value greater than 0, up to 1
- Formula: rows with value <= current / total rows
- Tied rows all get the share including every peer
- PERCENT_RANK counts rows strictly below; CUME_DIST counts at or below
- Filter cume_dist >= 0.8 in a subquery for the top 20% by value