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_2507200211126215410
    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_2507200211126215411 THEN @pMAIN_2507200211126215412 WHEN OrderQuartileGlobal = @pMAIN_2507200211126215413 THEN @pMAIN_2507200211126215414 WHEN OrderQuartileGlobal = @pMAIN_2507200211126215415 THEN @pMAIN_2507200211126215416 ELSE @pMAIN_2507200211126215417 END AS OrderQuartileDescriptionGlobal,
       OrderTertileWithinCountry AS OrderTertileWithinCountry,
       CASE WHEN OrderTertileWithinCountry = @pMAIN_2507200211126215418 THEN @pMAIN_2507200211126215419 WHEN OrderTertileWithinCountry = @pMAIN_250720021112621541_10 THEN @pMAIN_250720021112621541_11 ELSE @pMAIN_250720021112621541_12 END AS OrderTertileDescriptionWithinCountry
FROM CustomerOrderBuckets
ORDER BY Country ASC, OrderQuartileGlobal ASC, TotalOrders DESC;


            
        

Parameters (If used)

Name Value
@pMAIN_2507200211126215410 o.CustomerID
@pMAIN_2507200211126215411 1
@pMAIN_2507200211126215412 Top 25%
@pMAIN_2507200211126215413 2
@pMAIN_2507200211126215414 26-50%
@pMAIN_2507200211126215415 3
@pMAIN_2507200211126215416 50-75%
@pMAIN_2507200211126215417 Bottom 25%
@pMAIN_2507200211126215418 1
@pMAIN_2507200211126215419 Top 33%
@pMAIN_250720021112621541_10 2
@pMAIN_250720021112621541_11 Middle 33%
@pMAIN_250720021112621541_12 Bottom 33%

Query Results 1:

  CustomerID CompanyName Country TotalOrders OrderQuartileGlobal OrderQuartileDescriptionGlobal OrderTertileWithinCountry OrderTertileDescriptionWithinCountry
1 CACTU Cactus Comidas para llevar Argentina 0 1 Top 25% 1 Top 33%
2 OCEAN Océano Atlántico Ltda. Argentina 0 1 Top 25% 2 Middle 33%
3 RANCH Rancho grande Argentina 0 1 Top 25% 3 Bottom 33%
4 PICCO Piccolo und mehr Austria 0 1 Top 25% 1 Top 33%
5 ERNSH Ernst Handel Austria 0 1 Top 25% 2 Middle 33%
6 MAISD Maison Dewey Belgium 0 1 Top 25% 1 Top 33%
7 SUPRD Suprêmes délices Belgium 0 1 Top 25% 2 Middle 33%
8 TRADH Tradição Hipermercados Brazil 0 1 Top 25% 1 Top 33%
9 WELLI Wellington Importadora Brazil 0 1 Top 25% 1 Top 33%
10 QUEDE Que Delícia Brazil 0 1 Top 25% 1 Top 33%
11 QUEEN Queen Cozinha Brazil 0 1 Top 25% 2 Middle 33%
12 RICAR Ricardo Adocicados Brazil 0 1 Top 25% 2 Middle 33%
13 FAMIA Familia Arquibaldo Brazil 0 1 Top 25% 2 Middle 33%
14 GOURL Gourmet Lanchonetes Brazil 0 1 Top 25% 3 Bottom 33%
15 HANAR Hanari Carnes Brazil 0 1 Top 25% 3 Bottom 33%
16 COMMI Comércio Mineiro Brazil 0 1 Top 25% 3 Bottom 33%
17 BOTTM Bottom-Dollar Markets Canada 0 1 Top 25% 1 Top 33%
18 MEREP Mère Paillarde Canada 0 1 Top 25% 2 Middle 33%
19 LAUGB Laughing Bacchus Wine Cellars Canada 0 1 Top 25% 3 Bottom 33%
20 VAFFE Vaffeljernet Denmark 0 1 Top 25% 1 Top 33%
21 SIMOB Simons bistro Denmark 0 1 Top 25% 2 Middle 33%
22 WARTH Wartian Herkku Finland 0 1 Top 25% 1 Top 33%
23 WILMK Wilman Kala Finland 0 1 Top 25% 2 Middle 33%
24 VICTE Victuailles en stock France 0 1 Top 25% 1 Top 33%
25 VINET Vins et alcools Chevalier France 0 1 Top 25% 1 Top 33%
26 SPECD Spécialités du monde France 0 1 Top 25% 1 Top 33%
27 LACOR La corne d'abondance France 0 1 Top 25% 1 Top 33%
28 LAMAI La maison d'Asie France 0 1 Top 25% 2 Middle 33%
29 PARIS Paris spécialités France 0 1 Top 25% 2 Middle 33%
30 BONAP Bon app' France 0 1 Top 25% 2 Middle 33%
31 BLONP Blondesddsl père et fils France 0 1 Top 25% 2 Middle 33%
32 FOLIG Folies gourmandes France 0 1 Top 25% 3 Bottom 33%
33 DUMON Du monde entier France 0 1 Top 25% 3 Bottom 33%
34 FRANR France restauration France 0 1 Top 25% 3 Bottom 33%
35 FRANK Frankenversand Germany 0 1 Top 25% 1 Top 33%
36 BLAUS Blauer See Delikatessen Germany 0 1 Top 25% 1 Top 33%
37 ALFKI Alfreds Futterkiste Germany 0 1 Top 25% 1 Top 33%
38 DRACD Drachenblut Delikatessen Germany 0 1 Top 25% 1 Top 33%
39 OTTIK Ottilies Käseladen Germany 0 1 Top 25% 2 Middle 33%
40 QUICK QUICK-Stop Germany 0 1 Top 25% 2 Middle 33%
41 LEHMS Lehmanns Marktstand Germany 0 1 Top 25% 2 Middle 33%
42 KOENE Königlich Essen Germany 0 1 Top 25% 2 Middle 33%
43 MORGK Morgenstern Gesundkost Germany 0 1 Top 25% 3 Bottom 33%
44 TOMSP Toms Spezialitäten Germany 0 1 Top 25% 3 Bottom 33%
45 WANDK Die Wandernde Kuh Germany 0 1 Top 25% 3 Bottom 33%
46 HUNGO Hungry Owl All-Night Grocers Ireland 0 1 Top 25% 1 Top 33%
47 REGGC Reggiani Caseifici Italy 0 1 Top 25% 1 Top 33%
48 MAGAA Magazzini Alimentari Riuniti Italy 0 1 Top 25% 2 Middle 33%
49 FRANS Franchi S.p.A. Italy 0 1 Top 25% 3 Bottom 33%
50 CENTC Centro comercial Moctezuma Mexico 0 1 Top 25% 1 Top 33%
51 ANATR Ana Trujillo Emparedados y helados Mexico 0 1 Top 25% 1 Top 33%
52 ANTON Antonio Moreno Taquería Mexico 0 1 Top 25% 2 Middle 33%
53 PERIC Pericles Comidas clásicas Mexico 0 1 Top 25% 2 Middle 33%
54 TORTU Tortuga Restaurante Mexico 0 1 Top 25% 3 Bottom 33%
55 SANTG Santé Gourmet Norway 0 1 Top 25% 1 Top 33%
56 WOLZA Wolski Zajazd Poland 0 1 Top 25% 1 Top 33%
57 PRINI Princesa Isabel Vinhos Portugal 0 1 Top 25% 1 Top 33%
58 FURIB Furia Bacalhau e Frutos do Mar Portugal 0 1 Top 25% 2 Middle 33%
59 GALED Galería del gastrónomo Spain 0 1 Top 25% 1 Top 33%
60 GODOS Godos Cocina Típica Spain 0 1 Top 25% 1 Top 33%
61 FISSA FISSA Fabrica Inter. Salchichas S.A. Spain 0 1 Top 25% 2 Middle 33%
62 BOLID Bólido Comidas preparadas Spain 0 1 Top 25% 2 Middle 33%
63 ROMEY Romero y tomillo Spain 0 1 Top 25% 3 Bottom 33%
64 BERGS Berglunds snabbköp Sweden 0 1 Top 25% 1 Top 33%
65 FOLKO Folk och fä HB Sweden 0 1 Top 25% 2 Middle 33%
66 CHOPS Chop-suey Chinese Switzerland 0 1 Top 25% 1 Top 33%
67 RICSU Richter Supermarkt Switzerland 0 1 Top 25% 2 Middle 33%
68 ISLAT Island Trading UK 0 1 Top 25% 1 Top 33%
69 NORTS North/South UK 0 1 Top 25% 1 Top 33%
70 BSBEV B's Beverages UK 0 1 Top 25% 1 Top 33%
71 CONSH Consolidated Holdings UK 0 1 Top 25% 2 Middle 33%
72 AROUT Around the Horn UK 0 1 Top 25% 2 Middle 33%
73 EASTC Eastern Connection UK 0 1 Top 25% 3 Bottom 33%
74 SEVES Seven Seas Imports UK 0 1 Top 25% 3 Bottom 33%
75 TRAIH Trail's Head Gourmet Provisioners USA 0 1 Top 25% 1 Top 33%
76 SAVEA Save-a-lot Markets USA 0 1 Top 25% 1 Top 33%
77 SPLIR Split Rail Beer & Ale USA 0 1 Top 25% 1 Top 33%
78 THEBI The Big Cheese USA 0 1 Top 25% 1 Top 33%
79 THECR The Cracker Box USA 0 1 Top 25% 1 Top 33%
80 WHITC White Clover Markets USA 0 1 Top 25% 2 Middle 33%
81 GREAL Great Lakes Food Market USA 0 1 Top 25% 2 Middle 33%
82 HUNGC Hungry Coyote Import Store USA 0 1 Top 25% 2 Middle 33%
83 LONEP Lonesome Pine Restaurant USA 0 1 Top 25% 2 Middle 33%
84 LAZYK Lazy K Kountry Store USA 0 1 Top 25% 3 Bottom 33%
85 LETSS Let's Stop N Shop USA 0 1 Top 25% 3 Bottom 33%
86 RATTC Rattlesnake Canyon Grocery USA 0 1 Top 25% 3 Bottom 33%
87 OLDWO Old World Delicatessen USA 0 1 Top 25% 3 Bottom 33%
88 LILAS LILA-Supermercado Venezuela 0 1 Top 25% 1 Top 33%
89 LINOD LINO-Delicateses Venezuela 0 1 Top 25% 1 Top 33%
90 HILAA HILARION-Abastos Venezuela 0 1 Top 25% 2 Middle 33%
91 GROSR GROSELLA-Restaurante Venezuela 0 1 Top 25% 3 Bottom 33%