SQL CUME_DIST

What is SQL CUME_DIST?

CUME_DIST returns the cumulative distribution of a row within its partition, expressed as a value between 0 and 1 inclusive.

Welcome to the Galaxy, Guardian!
You'll be receiving a confirmation email

Follow us on twitter :)

Oops! Something went wrong while submitting the form.

Compatible dialects for SQL CUME_DIST: PostgreSQL, MySQL 8+, MariaDB 10.2+, SQL Server 2012+, Oracle 12c+, IBM Db2, DuckDB, Trino, Snowflake, BigQuery, Redshift

SQL CUME_DIST Full Explanation

CUME_DIST is an ANSI-standard window (analytic) function that shows the relative standing of each row within a result set partition. It calculates the proportion of rows with a sort value less than or equal to the current row’s value, including ties. When ordered ascending, the first row in a partition always returns the lowest non-zero fraction; the last row always returns 1.0. Duplicate sort keys receive the same CUME_DIST value because they share the same cumulative position. Unlike PERCENT_RANK, CUME_DIST counts the current row in both the numerator and denominator, ensuring the minimum possible result is 1 ÷ n rather than 0. The function must be used with an OVER() clause containing ORDER BY; PARTITION BY is optional. It is deterministic only when the ORDER BY list uniquely identifies row order.

SQL CUME_DIST Syntax

CUME_DIST() OVER (
    [PARTITION BY partition_expression [, ...]]
    ORDER BY sort_expression [ASC | DESC] [, ...]
)

SQL CUME_DIST Parameters

partition_expression (any column or expression) - divides the result set into independent partitions
sort_expression (any column or expression) - defines row order inside each partition; required

Example Queries Using SQL CUME_DIST

-- Example 1: Rank employees by salary within each department
SELECT 
    department_id,
    employee_id,
    salary,
    CUME_DIST() OVER (
        PARTITION BY department_id
        ORDER BY salary DESC
    ) AS salary_cume_dist
FROM employees;

-- Example 2: Find products in the top 25 percent of revenue
WITH revenues AS (
  SELECT 
      product_id,
      SUM(sales_amount) AS total_revenue
  FROM sales
  GROUP BY product_id
)
SELECT *
FROM (
  SELECT 
      product_id,
      total_revenue,
      CUME_DIST() OVER (ORDER BY total_revenue DESC) AS revenue_dist
  FROM revenues
) t
WHERE revenue_dist <= 0.25;

Expected Output Using SQL CUME_DIST

#VALUE!

Use Cases with SQL CUME_DIST

Identify top-n percentile performers or outliers
Build percentile-based segmentation (e.g., top 10 percent customers)
Create cumulative histograms without subqueries
Compare row positions while accounting for ties

Common Mistakes with SQL CUME_DIST

Omitting ORDER BY in the OVER clause (required)
Assuming the first row returns 0.0 (minimum is 1 ÷ row_count)
Expecting different values for tied rows – ties share the same cumulative distribution
Confusing CUME_DIST with PERCENT_RANK; they differ in numerator and minimum value

First Introduced In

SQL:2003 analytic functions

Frequently Asked Questions

What does CUME_DIST return?

It returns the proportion of rows in the partition with sort values less than or equal to the current row, expressed between 0 and 1.

How does CUME_DIST handle ties?

Rows that share the same ORDER BY value receive identical CUME_DIST results because they occupy the same cumulative position.

Why is my first row not showing 0.0?

CUME_DIST includes the current row in its calculation, so the smallest possible value is 1 divided by the total rows in the partition.

Can I filter on CUME_DIST in the same query?

Yes. Wrap the window function in a subquery or common table expression and then apply a WHERE clause, as shown in the examples.