CUME_DIST
intermediatewindow-functions
2 min read
What is CUME_DIST?
CUME_DIST() computes the cumulative distribution of a value within a partition. It returns the fraction of rows with values less than or equal to the current row's value. The formula is: (number of rows <= current value) / (total rows).
Syntax
SELECT column,
CUME_DIST() OVER (ORDER BY column) AS cum_dist
FROM table_name;When to Use
- Determining what percentage of values are at or below a given value
- Statistical distribution analysis
- Identifying the top or bottom N percent of values
Key Points
- Range — Values range from 1/N to 1.0 (never 0).
- Formula — (rows with value <= current) / total_rows.
- Ties — Rows with equal values share the same CUME_DIST.
- PARTITION BY — Compute distributions within groups.
- Use Case — "What fraction of students scored at or below this student?"
Guided Practice
Solve the challenge below. Use hints when stuck and check your answer for instant feedback.
Practice challengeGuided learning mode
CUME_DIST Challenge
Write a query that solve this task: what fraction of students are at or below each age?.
Expected result
Each student with the fraction of students at or below their age.
Hidden checks
- Returned rows and values
- Output columns and result shape
- Final database state after the query runs
Lesson guidance
What is CUME_DIST?
Initializing database...Each run starts from fresh sample data.
More Examples
Find the top 10% of products by price
Products in the top 10% price bracket.
Initializing database...Each run starts from fresh sample data.
Frequently Asked Questions
Can CUME_DIST be 0?
No. The minimum value is 1/N (at least one row is <= the current row — itself).
How is CUME_DIST different from NTILE?
CUME_DIST gives the relative position as a fraction. NTILE divides rows into N equal buckets and assigns a bucket number.