A more elegant way to apply Nested Ranking over SUM of a few columns

Question

Is there a more elegant way to apply nested ranking over a few different columns, sorted by the total of the leftmost column, then by the total within that the group? I am providing the screenshot re: sample data structure and the desired state.

Right now, I am using three different queries (first rank the Region by its Total. Then, a second by the State total, etc., and a third by the city) It is a pain to maintain, especially if I need to modify the order of the ranking as well as the date window. (Think Sales by Month, Sales by YTD, etc.)

Using RANK and windows function and partition, I can get the City Rank Within Region fairly easily, but to also get the state rank and region rank I start to struggle.

For those who are curious, I am doing the ranking in SQL instead of Tableau because it is almost impossible to mix ranking with a nested Top N + Other set up with Tableau window function or LOD.

CREATE TABLE RankingTest (
Region NVarChar(15),
[State] NVarChar(2),
City NVarChar(50),
Sales Int
);

INSERT INTO RankingTest (Region, [state], city, Sales) Values ('East','NY','New York',32);
INSERT INTO RankingTest (Region, [state], city, Sales) Values ('East','NY','New York',3);
INSERT INTO RankingTest (Region, [state], city, Sales) Values ('East','NY','Queens',4);
INSERT INTO RankingTest (Region, [state], city, Sales) Values ('East','MA','Boston',6);
INSERT INTO RankingTest (Region, [state], city, Sales) Values ('East','FL','Miama',7);
INSERT INTO RankingTest (Region, [state], city, Sales) Values ('West','WA','Seattle',13);
INSERT INTO RankingTest (Region, [state], city, Sales) Values ('West','WA','Bellevue',6);
INSERT INTO RankingTest (Region, [state], city, Sales) Values ('West','WA','Kirkland',8);
INSERT INTO RankingTest (Region, [state], city, Sales) Values ('West','OR','Portland',11);
INSERT INTO RankingTest (Region, [state], city, Sales) Values ('West','OR','Salem',3);
INSERT INTO RankingTest (Region, [state], city, Sales) Values ('West','CA','San Franscisco',6);
INSERT INTO RankingTest (Region, [state], city, Sales) Values ('West','CA','San Franscisco',9);

Current Query:

    WITH RegionRank AS (
        SELECT Region,
            DENSE_RANK() OVER (ORDER By SUM(Sales) DESC) AS Rank,
            SUM(Sales) AS Sales
        FROM RankingTest
        GROUP BY Region
    ),
    StateRank AS (
        SELECT RT.Region, State,
            DENSE_RANK() OVER (PARTITION BY RR.Rank ORDER BY RR.Rank, SUM(RT.Sales) DESC) AS Rank,
            SUM(RT.Sales) AS Sales
        FROM RankingTest RT LEFT JOIN RegionRank RR ON RT.Region = RR.Region
        GROUP BY RT.Region, RR.Rank, State
    ),
    CityRank AS (
        SELECT RT.Region, RR.Rank RegionRank, RR.Sales RegionSales, RT.State, 
        SR.Rank StateRank, SR.Sales StateSales, City,
        DENSE_RANK() OVER (PARTITION BY RR.Rank, SR.Rank ORDER BY RR.Rank, SR.Rank, SUM(RT.Sales) DESC) AS 
        Rank,
            SUM(RT.Sales) AS Sales
        FROM RankingTest RT
           LEFT JOIN RegionRank RR ON RT.Region = RR.Region
             LEFT JOIN StateRank SR ON RT.Region = SR.Region AND RT.State = SR.State
        GROUP BY RT.Region, RR.Rank, RR.Sales, RT.State, SR.Rank, SR.Sales, City
     )
     SELECT * FROM CityRank

SQL Fiddle

Have you had a look `DENSE_RANK`/`RANK` and the `OVER` clause? What is the query you're using at the moment? 3 joins would certainly not be the right choice here. — Thom A, Mar 29 '19 at 10:11
I can't read your data, which means the only way I can easily help you is to open an external link. Please add your data directly to your question as _text_. — Tim Biegeleisen, Mar 29 '19 at 10:11
@TimBiegeleisen there is a SQL Fiddle (link below the image). — Thom A, Mar 29 '19 at 10:11
Right, it's an external link, and a question (or answer) should not heavily rely on that. — Tim Biegeleisen, Mar 29 '19 at 10:12
I have added the current query. The actual data and query is of course more complicated but this should illustrate the problem. — The Lyrist, Apr 01 '19 at 14:05

Alex · Accepted Answer · 2019-06-25T05:40:40.750

Nested query should do the trick:

SELECT *,
    DENSE_RANK() OVER( ORDER BY SalesRegion DESC ) AS RankRegion,
    DENSE_RANK() OVER( PARTITION BY Region ORDER BY SalesState DESC ) AS RankState,
    DENSE_RANK() OVER( PARTITION BY Region, State ORDER BY SalesCity DESC ) AS RankCity
FROM(
    SELECT DISTINCT Region, State, City,
        SUM( Sales ) OVER( PARTITION BY city, State, Region ) as SalesCity,
        SUM( Sales ) OVER( PARTITION BY State, Region ) as SalesState,
        SUM( Sales ) OVER( PARTITION BY Region ) as SalesRegion
    FROM RankingTest ) AS DistinctSales
ORDER BY RankRegion, RankState, RankCity

Note that DISTINCT Region, State, City acts as a GROUP BY here.

Thanks for your solutions; it works great; it runs faster (according to the estimate execution plan). Can't wait to try that with the real data source.. — The Lyrist, Jun 26 '19 at 00:59

A more elegant way to apply Nested Ranking over SUM of a few columns

1 Answers1