Search code examples
sqlt-sqlrankingscoring

SQL - Group Values by Percentile/Merge Rankings

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

enter image description here

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.

enter image description here

Solution

  • 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 ()