CODEWITHSUNDEEP
sqlsql-server-2008
user172839
user172839

Reputation: 1065

Percentile ranking with multiple metrics

I'm looking to rank a number of records (ASX stocks) using a number of metrics using percentile ranking. I've used the following website to calculate the percentile rank, which works fine for me.

http://www.sqlservercentral.com/articles/PERCENT_RANK/141532/

Let's say for example, I want to rank them by highest earnings, lowest debt-to-equity ratio for now (I'll be ranking with probably more than 5 metrics). Let's say I want the highest earnings to have a higher importance of say 20%

Do I need to calculate the percentile ranking of each/metric seperately and then in this combine the two percentages and apply the 20% factor?

So for example: ABC Earnings percentile: 90% (one of the highest earners) Debt percentile: 10% (Has a high level of debt)

Then combine the 90% with the 10% to get an overall ranking?

; WITH CTE

AS
(
select
    e.SecurityCode,
    e.Earning,
    e.TotalDebtToEquity,
    rk1 = RANK() OVER (ORDER BY e.Earning),
    rk2 = RANK() OVER (ORDER BY e.TotalDebtToEquity),
    NR = COUNT(*) OVER ()
from Earning e 


SELECT
    SecurityCode,
    Earning,
    TotalDebtToEquity,
    PercentileEarningRank = ROUND((1.0*(rk1 - 1)/(nr-1))*100.00, 2),
    TotalDebtToEquity = ROUND((1.0*(rk2 - 1)/(nr-1))*100.00, 2)
FROM CTE

Upvotes: 0

Views: 416

Answers (1)

Nick
Nick

Reputation: 147206

What you want is a weighted average. If all the variables (say there are 5 called a,b,c,d and e) have the same importance, you would just compute:

out = (a+b+c+d+e)/5;

but if you wanted a to be 20% more important then everything else, you would compute

out = (1.2*a+b+c+d+e)/5.2;

note the divisor has to change in order to bring the result back into the 0-100 range (basically the divisor is the sum of the individual coefficents, in this case 1.2+1+1+1+1=5.2).

Upvotes: 2

Related Questions