Cumulative Distribution

Concepts covered: Cumulative Distribution

RANK says a driver is 5th; CUME_DIST says the driver earns at least as much as 83% of drivers. The second number is the one a statement or a dashboard shows, because it means the same thing in a city of 60 drivers and a city of 60,000. CUME_DIST and PERCENT_RANK both compute it, and they differ only in how they count the current row and its ties.

The 2 formulas

N is the row count of the partition

• CUME_DIST() = (rows with a value at or below this row's value) / N. It runs from 1/N for the lowest row to 1 for the highest.
• PERCENT_RANK() = (RANK - 1) / (N - 1). It runs from 0 for the lowest row to 1 for the highest, and a partition of 1 row gets 0.

Both functions sort the partition by the ORDER BY columns and give tied rows the same value. CUME_DIST counts the whole tie group as being at or below, so ties take the largest fraction in their group; PERCENT_RANK is built on RANK, so ties take the smallest. Here are 6 drivers' weekly pay, with 2 tied at 500.

SELECT
  driver_id,
  weekly_pay,
  CUME_DIST() OVER (ORDER BY weekly_pay) AS cume_dist,
  PERCENT_RANK() OVER (ORDER BY weekly_pay) AS pct_rank
FROM driver_earnings

Read d5's row: 5 of 6 drivers earn 900 or less, so CUME_DIST is 0.833, and d5 outranks 4 of the other 5 drivers, so PERCENT_RANK is 0.8. The tied pair shows the split: CUME_DIST says half the drivers earn 500 or less, which includes both of them; PERCENT_RANK says each of them outranks 1 of the 5 others.

From a fraction to a percentile question

'Top 10% of drivers in their city' is a comparison on CUME_DIST over a city partition, and like any window output it has to be filtered or flagged in an outer query. Ordering descending flips the meaning: CUME_DIST() OVER (ORDER BY weekly_pay DESC) is the fraction of drivers earning at least this much, so the top earner gets 1/N and the top decile is cume_desc <= 0.1.

WITH scored AS (
  SELECT
    driver_id,
    city,
    weekly_pay,
    CUME_DIST() OVER (PARTITION BY city ORDER BY weekly_pay) AS city_cume
  FROM driver_earnings
)
SELECT
  driver_id,
  city,
  weekly_pay,
  ROUND(city_cume, 3) AS city_cume,
  city_cume > 0.9 AS top_decile
FROM scored
ORDER BY city, weekly_pay

In SF both d2 and d3 are flagged, because CUME_DIST gives a tie group its upper bound and the pair sits at 1.0 together. With 3 drivers a decile has no meaning; on a city of 4,000 drivers the same flag marks about 400 of them, plus whoever ties at the boundary.

CUME_DIST and PERCENT_RANK

• A per-row position as a fraction of the partition
• Tied rows share one value
• Exact, computed from the sorted partition

NTILE and APPROX_PERCENTILE

• NTILE: a bucket number, and ties can split across 2 buckets
• APPROX_PERCENTILE: one value per group, the pay at the 90th percentile
• Use it to find a threshold, then compare rows to it

Choose by the question. 'Where does this driver stand' is CUME_DIST. 'What pay marks the 90th percentile' is APPROX_PERCENTILE(weekly_pay, 0.9) over the group, a single number. 'Split drivers into 4 equal groups for an experiment' is NTILE(4), which puts 2 tied drivers in different buckets when the split falls between them, something CUME_DIST never does.

Table t holds the values 10, 20, 20, 20, 30, 40, 50, 60. What is cd for each row where v = 20?

SELECT
  v,
  CUME_DIST() OVER (ORDER BY v) AS cd
FROM t

• 0.25, because the first 20 sits in position 2 of the 8 rows.
• 0.143, because the 20s rank second and (2 - 1) / (8 - 1) = 1/7.
• 0.375, because 3 rows hold the value 20 and 3 / 8 = 0.375.
• 0.5, because 4 of the 8 rows hold a value at or below 20.

About this lesson

This section is part of the Window Functions: Advanced lesson on DataDriven, the home of data engineering. Lessons cover SQL, Python, Spark, data modeling and pipeline architecture, and each section pairs its explanation with worked examples and practice you run as you go.