NTILE SQL function

1. Usage of NTILE to show order distribution within a country.

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  

 ),  

 CustomerOrderBuckets AS (  

     SELECT  

         CustomerID,  

         CompanyName,  

         Country,  

         TotalOrders,  

         NTILE(1) OVER (ORDER BY TotalOrders DESC) AS OrderQuartileGlobal,  

         NTILE(3) OVER (PARTITION BY Country ORDER BY TotalOrders DESC) AS OrderTertileWithinCountry  

     FROM  

         CustomerOrderCounts  

 )  

 SELECT  

     CustomerID,  

     CompanyName,  

     Country,  

     TotalOrders,  

     OrderQuartileGlobal,  

     CASE  

         WHEN OrderQuartileGlobal = 1 THEN 'Top 25%'  

         WHEN OrderQuartileGlobal = 2 THEN '26-50%'  

         WHEN OrderQuartileGlobal = 3 THEN '51-75%'  

         ELSE 'Bottom 25%'  

     END AS OrderQuartileDescriptionGlobal,  

     OrderTertileWithinCountry,  

     CASE  

         WHEN OrderTertileWithinCountry = 1 THEN 'Top 33%'  

         WHEN OrderTertileWithinCountry = 2 THEN 'Middle 33%'  

         ELSE 'Bottom 33%'  

     END AS OrderTertileDescriptionWithinCountry  

 FROM  

     CustomerOrderBuckets  

 ORDER BY  

     Country,  

     OrderQuartileGlobal,  

     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("o.CustomerID"))  

         })  

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

     )  

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

         .Select()  

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

         .Column(new NTILE(1).ORDER_BY(new OrderBy().Set(new Column("TotalOrders"), OrderByDirection.DESCENDING)), "OrderQuartileGlobal")  

         .Column(new NTILE(3).PARTITION_BY(new Column("Country")).ORDER_BY(new OrderBy().SetColumnDescending("TotalOrders")), "OrderTertileWithinCountry")  

         .From("CustomerOrderCounts")  

     )  

     .Select()  

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

     .Column(new CASE()  

         .When(new Column("OrderQuartileGlobal").Equale(1))  

             .Then("Top 25%")  

         .When(new Column("OrderQuartileGlobal").Equale(2))  

             .Then("26-50%")  

         .When(new Column("OrderQuartileGlobal").Equale(3))  

             .Then("50-75%")  

         .Else("Bottom 25%"), "OrderQuartileDescriptionGlobal")  

     .Column("OrderTertileWithinCountry", "OrderTertileWithinCountry")  

     .Column(new CASE()  

         .When(new Column("OrderTertileWithinCountry").Equale(1))  

             .Then("Top 33%")  

         .When(new Column("OrderTertileWithinCountry").Equale(2))  

             .Then("Middle 33%")  

         .Else("Bottom 33%"), "OrderTertileDescriptionWithinCountry")  

     .From("CustomerOrderBuckets")  

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

         .SetColumnAscending("OrderQuartileGlobal")  

         .SetColumnDescending("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 = @pMAIN_2507192101282698470
    GROUP BY c.CustomerID, c.CompanyName, c.Country),
 CustomerOrderBuckets
AS (SELECT CustomerID,
           CompanyName,
           Country,
           TotalOrders,
           NTILE(1) OVER (ORDER BY TotalOrders DESC) AS OrderQuartileGlobal,
           NTILE(3) OVER (PARTITION BY Country ORDER BY TotalOrders DESC) AS OrderTertileWithinCountry
    FROM CustomerOrderCounts)
SELECT CustomerID,
       CompanyName,
       Country,
       TotalOrders,
       OrderQuartileGlobal,
       CASE WHEN OrderQuartileGlobal = @pMAIN_2507192101282698471 THEN @pMAIN_2507192101282698472 WHEN OrderQuartileGlobal = @pMAIN_2507192101282698473 THEN @pMAIN_2507192101282698474 WHEN OrderQuartileGlobal = @pMAIN_2507192101282698475 THEN @pMAIN_2507192101282698476 ELSE @pMAIN_2507192101282698477 END AS OrderQuartileDescriptionGlobal,
       OrderTertileWithinCountry AS OrderTertileWithinCountry,
       CASE WHEN OrderTertileWithinCountry = @pMAIN_2507192101282698478 THEN @pMAIN_2507192101282698479 WHEN OrderTertileWithinCountry = @pMAIN_250719210128269847_10 THEN @pMAIN_250719210128269847_11 ELSE @pMAIN_250719210128269847_12 END AS OrderTertileDescriptionWithinCountry
FROM CustomerOrderBuckets
ORDER BY Country ASC, OrderQuartileGlobal ASC, TotalOrders DESC;


            
        

Parameters (If used)

Query Results 1: