Hands-On Data Science with SQL Server 2017

The PERCENT_RANK and CUME_DIST functions

Using these functions is not as common and obvious as using aggregate functions. The syntax for both functions uses the same OVER clause, as we learned earlier in this chapter.

The PERCENT_RANK function works in four steps:

1. The function calculates the number of records minus 1 (the evaluated value is omitted from the record count)—let's mark this as X
2. The function finds the position of the last occurrence of a certain value in the sorted dataset
3. The function counts the number of records under the evaluated value—let's mark this as N
4. The function computes the N/X fraction, which is the result of the computation

This process may look confusing, so the best way to make the PERCENT_RANK function clear ...