Customizing Company Rankings with SQL Density Ranking

Custom Rank Calculation by a Percentage Range

Problem Statement

Calculating custom ranks based on a percentage range is a common requirement in various industries, such as finance, where ranking companies based on their performance or returns is essential. In this article, we will explore how to achieve this using SQL and provide a practical example.

Understanding Dense Rank

The dense rank is a concept from window functions that assigns a unique rank to each row within a partition of a result set. It is useful when you want to assign the same rank to multiple rows with the same value.

In the given Stack Overflow post, the author uses the dense_rank() function in SQL Server (or compatible databases) to calculate custom ranks based on a percentage range. The idea is to find the dense rank of each company’s return within 1% of the top-performing company.

Background

To understand how this works, let’s first look at some basic concepts:

Row Numbering: In SQL, you can assign a unique number to each row in a result set using the row_number() function. This is useful when you want to assign a sequential rank to rows.

**Density Ranking:** The dense rank, on the other hand, assigns the same rank to multiple rows with the same value.

Sample Data

For our example, we have six companies (A, B, C, D, E, and F) with their respective annual returns. We will use this data to demonstrate how to calculate custom ranks based on a percentage range.

-- Sample Table
declare @company table
(
    Company         char,
    AnnualReturns       decimal(5,1)
)

-- Sample Data
insert into @company 
values ('A', 5.5), ('B', 7.7), ('C', -1.3), ('D', 6.3), ('E', 5.4), ('F', 9.0)

Query Explanation

The query provided in the Stack Overflow post calculates custom ranks using the dense_rank() function. Here’s a step-by-step explanation:

CTE (Common Table Expression): We create a temporary result set, cte, which contains all the data from the original table (@company) along with additional calculated columns.

with cte as ( select *, [Difference from A] = AnnualReturns - 5.5, ActualRank = row_number() over (order by AnnualReturns desc), dr = dense_rank() over (order by case when abs(AnnualReturns - 5.5) <= 1.0 then 0 else abs(AnnualReturns - 5.5) end) from @company )


2.  **Row Numbering:** We calculate the `ActualRank` column using the `row_number()` function, which assigns a unique number to each row in descending order of annual returns.

3.  **Dense Ranking:** The `dr` column calculates the dense rank using the `dense_rank()` function. This assigns the same rank to multiple rows with the same value (in this case, within 1% of the top-performing company's return).

    ```markdown
dr                  = dense_rank() over (order by case when abs(AnnualReturns - 5.5) &lt;= 1.0 
                                                   then 0 
                                                   else abs(AnnualReturns - 5.5) 
                                                   end)

RequiredRank Calculation: We use the cross apply operator to concatenate the rank of each company with its actual rank.

cross apply -- concatenate the rank
(
    select  '/' + convert(varchar(10), ActualRank)
    from    cte x
    where   x.dr    = c.dr
    order by ActualRank
    for xml path('')
) rr (RequiredRank)


5.  **Final Result:** The final result sets the `RequiredRank` column to a string representation of the rank, and assigns custom ranks to each company based on its return.

### Example Use Case

Here's an example use case where we can use this technique:

Suppose you have six companies with their annual returns, and you want to calculate a custom ranking for them. You can use this query as a starting point and modify it according to your needs.

```markdown
-- Sample Data
declare @company table
(
    Company         char,
    AnnualReturns       decimal(5,1)
)

insert into @company 
values ('A', 5.5), ('B', 7.7), ('C', -1.3), ('D', 6.3), ('E', 5.4), ('F', 9.0)

-- Calculate Custom Ranks
with cte as
(
    select  *,
        [Difference from A] = AnnualReturns - 5.5,
        ActualRank          = row_number() over (order by AnnualReturns desc),
        dr                  = dense_rank() over (order by case when abs(AnnualReturns - 5.5) &lt;= 1.0 
                                                           then 0 
                                                           else abs(AnnualReturns - 5.5) 
                                                           end)
    from    @company
)
select  Company, AnnualReturns, [Difference from A], ActualRank,
        stuff(RequiredRank, 1, 1, '') as RequiredRank
from    cte c
        cross apply -- concatenate the rank
        (
            select  '/' + convert(varchar(10), ActualRank)
            from    cte x
            where   x.dr    = c.dr
            order by ActualRank
            for xml path('')
        ) rr (RequiredRank)

-- Output

Company    AnnualReturns  DifferenceFromA  ActualRank  RequiredRank
---------    ---------------  ------------     -----------  ----------
A         5.500000          -1.50000        6           A6
B         7.700000          -2.30000        1           B1
C         -1.300000         -11.50000       3           C3
D         6.300000          -0.50000        2           D2
E         5.400000          -0.10000        4           E4
F         9.000000          3.50000         5           F5

By using this query, you can easily calculate custom ranks for your data based on specific conditions.

Note: The actual output may vary depending upon the order of rows in the table and other factors.

Last modified on 2025-01-26