DENSE RANK SQL function

1. Usage of DENSE_RANK to show cumulative rank distribution of orders.

SQL Server Query 1

            
 WITH CustomerOrderCounts AS (  

     SELECT  

         c.CustomerID,  

         c.CompanyName,  

         c.Country,  

         COUNT(o.OrderID) AS TotalOrders  

     FROM  

         Customers c  

     LEFT JOIN  

         Orders o ON c.CustomerID = o.CustomerID  

     GROUP BY  

         c.CustomerID,  

         c.CompanyName,  

         c.Country  

 ),  

 OrderRankWithinCountry AS (  

     SELECT  

         CustomerID,  

         CompanyName,  

         Country,  

         TotalOrders,  

         DENSE_RANK() OVER (PARTITION BY Country ORDER BY TotalOrders DESC) AS OrderRank  

     FROM  

         CustomerOrderCounts  

 ),  

 RankDistributionWithinCountry AS (  

     SELECT  

         Country,  

         OrderRank,  

         COUNT(*) AS NumberOfCustomersAtRank,  

         CUME_DIST() OVER (PARTITION BY Country ORDER BY OrderRank) AS CumulativeRankDistribution  

     FROM  

         OrderRankWithinCountry  

     GROUP BY  

         Country,  

         OrderRank  

 )  

 SELECT  

     orc.CustomerID,  

     orc.CompanyName,  

     orc.Country,  

     orc.TotalOrders,  

     orc.OrderRank,  

     rdc.NumberOfCustomersAtRank,  

     rdc.CumulativeRankDistribution  

 FROM  

     OrderRankWithinCountry orc  

 JOIN  

     RankDistributionWithinCountry rdc ON orc.Country = rdc.Country AND orc.OrderRank = rdc.OrderRank  

 ORDER BY  

     orc.Country,  

     orc.OrderRank,  

     orc.TotalOrders DESC;  

            
        

Create SQL query with SqlQueryBuilder 1

            
 var (sql1, parameters1) = new SqlQueryBuilder()  

     .WithCTETable(new Table("CustomerOrderCounts"), new SqlQueryBuilder()  

         .Select()  

         .Columns("c.CustomerID", "c.CompanyName", "c.Country")  

         .Column(new COUNT(new Column("o.OrderID")), "TotalOrders")  

         .From("Customers", "c")  

         .Join(new List<IJoin>()  

         {  

             new LEFTJOIN().TableName(new Table("Orders","o"))  

                 .On(new Column("c.CustomerID").Equale(new Column("o.CustomerID")))  

         })  

         .GroupBy(new GroupBy("c.CustomerID", "c.CompanyName", "c.Country"))  

     )  

     .WithCTETable(new Table("OrderRankWithinCountry"), new SqlQueryBuilder()  

         .Select()  

         .Columns("CustomerID", "CompanyName", "Country", "TotalOrders")  

         .Column(new DENSE_RANK().PARTITION_BY(new Column("Country")).ORDER_BY(new OrderBy().SetColumnAscending("TotalOrders")), "OrderRank")  

         .From("CustomerOrderCounts")  

     )  

     .WithCTETable(new Table("RankDistributionWithinCountry"), new SqlQueryBuilder()  

         .Select()  

         .Columns("Country", "OrderRank")  

         .Column(new COUNT(new Column("Country")), "NumberOfCustomersAtRank")  

         .Column(new CUME_DIST().PARTITION_BY(new Column("Country"))  

             .ORDER_BY(new OrderBy().SetColumnAscending("OrderRank")), "CumulativeRankDistribution")  

         .From("OrderRankWithinCountry")  

         .GroupBy(new GroupBy("Country", "OrderRank"))  

     )  

     .Select()  

     .Columns("orc.CustomerID", "orc.CompanyName", "orc.Country", "orc.TotalOrders", "orc.OrderRank", "rdc.NumberOfCustomersAtRank", "rdc.CumulativeRankDistribution")  

     .From("OrderRankWithinCountry", "orc")  

     .Join(new List<IJoin>()  

     {  

         new INNERJOIN().TableName(new Table("RankDistributionWithinCountry","rdc"))  

             .On(new Column("orc.Country").Equale(new Column("rdc.Country"))  

             .AND(new Column("orc.OrderRank").Equale(new Column("rdc.OrderRank")))  

     })  

     .OrderBy(new OrderBy().SetColumnAscending("orc.Country")  

         .SetColumnAscending("orc.OrderRank")  

         .SetColumnDescending("orc.TotalOrders"))  

     .Build();  

            
        

Query build by SqlQueryBuilder 1

            
WITH CustomerOrderCounts
AS (SELECT c.CustomerID,
           c.CompanyName,
           c.Country,
           COUNT(o.OrderID) AS TotalOrders
    FROM Customers AS c
         LEFT OUTER JOIN
         Orders AS o
         ON c.CustomerID = o.CustomerID
    GROUP BY c.CustomerID, c.CompanyName, c.Country),
 OrderRankWithinCountry
AS (SELECT CustomerID,
           CompanyName,
           Country,
           TotalOrders,
           DENSE_RANK() OVER (PARTITION BY Country ORDER BY TotalOrders ASC) AS OrderRank
    FROM CustomerOrderCounts),
 RankDistributionWithinCountry
AS (SELECT Country,
           OrderRank,
           COUNT(Country) AS NumberOfCustomersAtRank,
           CUME_DIST() OVER (PARTITION BY Country ORDER BY OrderRank ASC) AS CumulativeRankDistribution
    FROM OrderRankWithinCountry
    GROUP BY Country, OrderRank)
SELECT orc.CustomerID,
       orc.CompanyName,
       orc.Country,
       orc.TotalOrders,
       orc.OrderRank,
       rdc.NumberOfCustomersAtRank,
       rdc.CumulativeRankDistribution
FROM OrderRankWithinCountry AS orc
     INNER JOIN
     RankDistributionWithinCountry AS rdc
     ON orc.Country = rdc.Country
        AND orc.OrderRank = rdc.OrderRank
ORDER BY orc.Country ASC, orc.OrderRank ASC, orc.TotalOrders DESC;


            
        

Parameters (If used)

Query Results 1: