RANK SQL function

1. Usage of RANK_Function to show formatted average order at rank.

SQL Server Query 1

            
 WITH CustomerOrderCounts AS  

 (  

 	SELECT c.CustomerID, CompanyName, c.Country, COUNT(o.OrderID) AS TotalOrders   

 	FROM Customers c   

 		LEFT JOIN Orders o ON c.CustomerID = o.CustomerID  

 	GROUP BY c.CustomerID, CompanyName, c.Country  

 ),  

 OrderRankWithinCountry AS  

 (  

 	SELECT CustomerID, CompanyName, Country, TotalOrders,  

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

 	FROM CustomerOrderCounts  

 ),  

 RankAnalysisWithinCountry AS  

 (  

 	SELECT Country, OrderRank, MIN(TotalOrders) AS MinOrdersAtRank, MAX(TotalOrders) AS MaxOrdersAtRank,  

 	COUNT(TotalOrders) AS NumberOfCustomersAtRank, AVG(CAST(TotalOrders AS decimal(10,2))) AS AvgOrdersAtRank  

 	FROM OrderRankWithinCountry  

 	GROUP BY Country, OrderRank  

 )  

 SELECT orc.CustomerID, orc.CompanyName, orc.Country, orc.TotalOrders, orc.OrderRank, rac.MinOrdersAtRank, rac.MaxOrdersAtRank, rac.NumberOfCustomersAtRank,  

 FORMAT(rac.AvgOrdersAtRank, 'N2') AS AvgOrdersAtRankFormatted  

 FROM OrderRankWithinCountry orc  

 	JOIN RankAnalysisWithinCountry rac ON orc.Country = rac.Country AND orc.OrderRank = rac.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 INNERJOIN().TableName(new Table("Orders","o"))  

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

         })  

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

     )  

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

         .Select()  

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

         .Column(new RANK().PARTITION_BY(new Column("Country")).ORDER_BY(new OrderBy().SetColumnDescending("TotalOrders")), "OrderRank")  

         .From("CustomerOrderCounts")  

     )  

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

         .Select()  

         .Columns("Country", "OrderRank")  

         .Column(new MIN(new Column("TotalOrders")), "MinOrdersAtRank")  

         .Column(new MAX(new Column("TotalOrders")), "MaxOrdersAtRank")  

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

         .Column(new AVG(new CAST(new Column("TotalOrders"), SqlDataType.DECIMAL, new Tuple<int, int>(10, 2))), "AvgOrdersAtRank")  

         .From("OrderRankWithinCountry")  

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

     )  

     .Select()  

     .Columns("orc.CustomerID", "orc.CompanyName", "orc.Country", "orc.TotalOrders", "orc.OrderRank", "rac.MinOrdersAtRank", "rac.MaxOrdersAtRank", "rac.NumberOfCustomersAtRank")  

     .Column(new FORMAT(new Column("rac.AvgOrdersAtRank"), "N2"), "AvgOrdersAtRankFormatted")  

     .From("OrderRankWithinCountry", "orc")  

     .Join(new List<IJoin>()  

     {  

         new INNERJOIN().TableName(new Table("RankAnalysisWithinCountry","rac"))  

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

             .AND(new Column("orc.OrderRank").Equale(new Column("rac.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, CompanyName, c.Country),
 OrderRankWithinCountry
AS (SELECT CustomerID,
           CompanyName,
           Country,
           TotalOrders,
           RANK() OVER (PARTITION BY Country ORDER BY TotalOrders DESC) AS OrderRank
    FROM CustomerOrderCounts),
 RankAnalysisWithinCountry
AS (SELECT Country,
           OrderRank,
           MIN(TotalOrders) AS MinOrdersAtRank,
           MAX(TotalOrders) AS MaxOrdersAtRank,
           COUNT(TotalOrders) AS NumberOfCustomersAtRank,
           AVG(CAST (TotalOrders AS DECIMAL (10, 2))) AS AvgOrdersAtRank
    FROM OrderRankWithinCountry
    GROUP BY Country, OrderRank)
SELECT orc.CustomerID,
       orc.CompanyName,
       orc.Country,
       orc.TotalOrders,
       orc.OrderRank,
       rac.MinOrdersAtRank,
       rac.MaxOrdersAtRank,
       rac.NumberOfCustomersAtRank,
       FORMAT(rac.AvgOrdersAtRank, @pMAIN_2507192105573248440) AS AvgOrdersAtRankFormatted
FROM OrderRankWithinCountry AS orc
     INNER JOIN
     RankAnalysisWithinCountry AS rac
     ON orc.Country = rac.Country
        AND orc.OrderRank = rac.OrderRank
ORDER BY orc.Country ASC, orc.OrderRank ASC, orc.TotalOrders DESC;


            
        

Parameters (If used)

Query Results 1: