Solve “Deduplicate events with ROW_NUMBER”

Free. Runs in your browser, no install.

SQL · Window Functions

PERCENT_RANK vs CUME_DIST

Mediumpercent_rankcume_distdistribution

Interview question

What is the difference between PERCENT_RANK and CUME_DIST?

How to answer

The short answer

PERCENT_RANK() measures relative rank.

Formula:

Output
(rank - 1) / (number_of_rows - 1)

It ranges from 0 to 1 when there is more than 1 row.

CUME_DIST() tells you the proportion of rows whose ordering value is less than or equal to the current row's value.

Simple difference

> PERCENT_RANK = position based on rank > CUME_DIST = percentage of rows at or below this value

Ties can make their results noticeably different.

Lock it in with a real drill

You remember an answer longer once you have used it. Try a hands-on problem on this concept.

Open related drill