I continue a series on window functions in SQL Server:
CUME_DIST()doesn’t show 0 for the smallest record. The reason for this is in the definition:CUME_DIST()tells us how far along we are in describing the entire set—that is, what percentage of values have we covered so far. This percentage is always greater than 0. By contrast,PERCENT_RANK()forces the lowest value to be 0 and the highest value to be 1.Another thing to note is ties. There are 117 values for customer 1 in my dataset. Rows 5 and 6 both have a percent rank of 0.0344, which is approximately rank 4 (remembering that we start from 0, not 1). Both rows 5 and 6 have the same rank of 4, and then we move up to a rank of 6. Meanwhile, for cumulative distribution, we see that rows 5 and 6 have a cumulative distribution of 6/117 = 0.5128. In other words,
PERCENT_RANK()ties get the lowest possible value, whereasCUME_DIST()ties get the highest possible value.
Click through for much more detail, including examples galore.