SQL - Group Values by Percentile/Merge Rankings

Question

I have multiple tables that contain the name of a company/attribute and a ranking.

I would like to write a piece of code which allows a range of Scores to be placed into specific Groups based on the percentile of the score in relationship to tables Score total. I provided a very easy use case to demonstrate what I am looking for, splitting a group of 10 companies into 5 groups, but I would like to scales this in order to apply the 5 groups to data sets with many rows WITHOUT having to specify values in a CASE statement.

Answer 1

You can use NTILE to divide the data into 5 buckets based on score. However, if the data can't be divided into equal number of bins or if there are ties, one of the groups will have more members.

SELECT t.*, NTILE(5) OVER(ORDER BY score) as grp
FROM tablename t

Read more about NTILE here

Answer 2

NTILE(5) OVER(ORDER BY score) might actually put rows with the same value into different quantiles (This is probably not what you want, at least I never liked that).

It's quite similar to

5 * (row_number() over (order by score) - 1) / count(*) over ()

but if the number of rows can't be evenly divided the remainder rows are added to the first quantiles when using NTILE and randomly for ROW_NUMBER.

To assign all the rows with the same value to the same quantile you need to do your own calculation:

5 * (rank() over (order by score) - 1) / count(*) over ()