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_2507200154055404920) AS OrderPercentRankFormatted,
       CASE WHEN OrderPercentRank <= @pMAIN_2507200154055404921 THEN @pMAIN_2507200154055404922 WHEN OrderPercentRank <= @pMAIN_2507200154055404923 THEN @pMAIN_2507200154055404924 WHEN OrderPercentRank <= @pMAIN_2507200154055404925 THEN @pMAIN_2507200154055404926 ELSE @pMAIN_2507200154055404927 END AS OrderFrequencySegment
FROM OrderPercentRankWithinCountry
ORDER BY Country ASC, OrderPercentRank ASC;


            
        

Parameters (If used)

Name Value
@pMAIN_2507200154055404920 P2
@pMAIN_2507200154055404921 0.25
@pMAIN_2507200154055404922 Bottom 25%
@pMAIN_2507200154055404923 0.5
@pMAIN_2507200154055404924 26-50%
@pMAIN_2507200154055404925 0.75
@pMAIN_2507200154055404926 51-75%
@pMAIN_2507200154055404927 Top 25%

Query Results 1:

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