Window PERCENT_RANK SQL function

1. Usage of PERCENT_RANK to show order frequency segment

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  

 ),  

 OrderPercentRankWithinCountry AS (  

     SELECT  

         CustomerID,  

         CompanyName,  

         Country,  

         TotalOrders,  

         PERCENT_RANK() OVER (PARTITION BY Country ORDER BY TotalOrders) AS OrderPercentRank  

     FROM  

         CustomerOrderCounts  

 )  

 SELECT  

     CustomerID,  

     CompanyName,  

     Country,  

     TotalOrders,  

     OrderPercentRank,  

     FORMAT(OrderPercentRank, 'P2') AS OrderPercentRankFormatted,  

     CASE  

         WHEN OrderPercentRank <= 0.25 THEN 'Bottom 25%'  

         WHEN OrderPercentRank <= 0.50 THEN '26-50%'  

         WHEN OrderPercentRank <= 0.75 THEN '51-75%'  

         ELSE 'Top 25%'  

     END AS OrderFrequencySegment  

 FROM  

     OrderPercentRankWithinCountry  

 ORDER BY  

     Country,  

     OrderPercentRank;  

            
        

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(new Column("c.CustomerID"), new Column("c.CompanyName"), new Column("c.Country")))  

     )  

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

         .Select()  

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

         .Column(new PERCENT_RANK().PARTITION_BY(new Column("Country")).OrderBy(new OrderBy().SetColumnAscending("TotalOrders")), "OrderPercentRank")  

         .From("CustomerOrderCounts")  

     )  

     .Select()  

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

     .Column(new FORMAT(new Column("OrderPercentRank"), "P2"), "OrderPercentRankFormatted")  

     .Column(new CASE()  

         .When(new Column("OrderPercentRank").LessThanOrEqualeTo(0.25))  

             .Then("Bottom 25%")  

         .When(new Column("OrderPercentRank").LessThanOrEqualeTo(0.50))  

             .Then("26-50%")  

         .When(new Column("OrderPercentRank").LessThanOrEqualeTo(0.75))  

             .Then("51-75%")  

         .Else("Top 25%"), "OrderFrequencySegment")  

     .From("OrderPercentRankWithinCountry")  

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

     .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),
 OrderPercentRankWithinCountry
AS (SELECT CustomerID,
           CompanyName,
           Country,
           TotalOrders,
           PERCENT_RANK() OVER (PARTITION BY Country ORDER BY TotalOrders ASC) AS OrderPercentRank
    FROM CustomerOrderCounts)
SELECT CustomerID,
       CompanyName,
       Country,
       TotalOrders,
       OrderPercentRank,
       FORMAT(OrderPercentRank, @pMAIN_2507192040571581010) AS OrderPercentRankFormatted,
       CASE WHEN OrderPercentRank <= @pMAIN_2507192040571581011 THEN @pMAIN_2507192040571581012 WHEN OrderPercentRank <= @pMAIN_2507192040571581013 THEN @pMAIN_2507192040571581014 WHEN OrderPercentRank <= @pMAIN_2507192040571581015 THEN @pMAIN_2507192040571581016 ELSE @pMAIN_2507192040571581017 END AS OrderFrequencySegment
FROM OrderPercentRankWithinCountry
ORDER BY Country ASC, OrderPercentRank ASC;


            
        

Parameters (If used)

Query Results 1: