MAX SQL function

1. Usage of MAX to find orders 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 LEFTJOIN().TableName(new Table("Orders","o"))  

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

        })  

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

Name Value
@pMAIN_2507200155145702040 N2

Query Results 1:

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

2. Usage of MAX to find product similarity

SQL Server Query 2

            
WITH ProductData AS (  

    SELECT  

        ProductID,  

        ProductName,  

        UnitPrice,  

        UnitsInStock,		  

        -- Normalize UnitPrice and UnitsInStock to a range of 0 to 1  

        CAST(UnitPrice AS DECIMAL(10, 4)) / (SELECT MAX(UnitPrice) FROM Products) AS NormalizedPrice,  

        CAST(UnitsInStock AS DECIMAL(10, 4)) / (SELECT CAST(MAX(UnitsInStock) AS DECIMAL(10,4)) FROM Products) AS NormalizedStock  

    FROM Products  

    WHERE UnitsInStock > 0 AND UnitPrice > 0  --important to avoid nulls in normalization  

),  

ProductPairs AS (  

    SELECT  

        p1.ProductID AS Product1,  

        p1.ProductName AS ProductName1,  

        p2.ProductID AS Product2,  

        p2.ProductName AS ProductName2,  

        -- Calculate a value similar to cosine similarity (but with our normalized data)  

        (p1.NormalizedPrice * p2.NormalizedPrice + p1.NormalizedStock * p2.NormalizedStock) /  

        (  

            SQRT(p1.NormalizedPrice * p1.NormalizedPrice + p1.NormalizedStock * p1.NormalizedStock) *  

            SQRT(p2.NormalizedPrice * p2.NormalizedPrice + p2.NormalizedStock * p2.NormalizedStock)  

        ) AS SimilarityValue  

    FROM ProductData p1  

    CROSS JOIN ProductData p2  

    WHERE p1.ProductID < p2.ProductID  

),  

AcosResult as (  

    SELECT  

        Product1,  

        ProductName1,  

        Product2,  

        ProductName2,  

        SimilarityValue,  

        -- Ensure the value is within the valid range for ACOS  

        CASE  

            WHEN SimilarityValue > 1 THEN 1  

            WHEN SimilarityValue < -1 THEN -1  

            ELSE SimilarityValue  

        END AS AdjustedSimilarityValue,  

        ACOS(  

            CASE  

            WHEN SimilarityValue > 1 THEN 1  

            WHEN SimilarityValue < -1 THEN -1  

            ELSE SimilarityValue  

            END  

            ) AS SimilarityAngleInRadians  

    FROM ProductPairs  

  

)  

SELECT TOP 10  

    Product1,  

    ProductName1,  

    Product2,  

    ProductName2,  

    SimilarityAngleInRadians,  

    DEGREES(SimilarityAngleInRadians) AS SimilarityAngleInDegrees  

FROM AcosResult  

ORDER BY SimilarityAngleInRadians;

Create SQL query with SqlQueryBuilder 2

            
var (sql2, parameters2) = new SqlQueryBuilder()  

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

        .Select()  

        .Columns("ProductID", "ProductName", "UnitPrice", "UnitsInStock")  

        .Column(new ColumnArithmatic()  

            .SqlFunction(new CAST(new Column("UnitPrice"), SqlDataType.DECIMAL, new Tuple<int, int>(10, 4)))  

            .DIVIDE()  

            .Column(new SqlQueryBuilder().Select()  

                .Column(new MAX(new Column("UnitPrice")), "NormalizedPrice")  

                .From("Products")  

            )  

        , "NormalizedPrice")  

        .Column(new ColumnArithmatic()  

            .SqlFunction(new CAST(new Column("UnitsInStock"), SqlDataType.DECIMAL, new Tuple<int, int>(10, 4)))  

            .DIVIDE()  

            .Column(new SqlQueryBuilder().Select()  

                .Column(new CAST(new MAX(new Column("UnitsInStock")), SqlDataType.DECIMAL, new Tuple<int, int>(10, 4)), "NormalizedStock")  

                .From("Products")  

            )  

        , "NormalizedStock")  

        .From("Products")  

        .Where(new Where(new Column("UnitsInStock").GreaterThan(0))  

            .AND(new Column("UnitPrice").GreaterThan(0)))  

    )  

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

        .Select()  

        .Column("p1.ProductID", "Product1")  

        .Column("p1.ProductName", "ProductName1")  

        .Column("p2.ProductID", "Product2")  

        .Column("p2.ProductName", "ProductName2")  

        .Column(new ColumnArithmatic()  

            .StartBracket()  

                .Column("p1.NormalizedPrice").MULTIPLY().Column("p2.NormalizedPrice")  

                .ADD().Column("p1.NormalizedStock").MULTIPLY().Column("p2.NormalizedStock")  

            .EndBracket().DIVIDE()  

            .StartBracket()  

                .SqlFunction(new SQRT(new ColumnArithmatic().Column("p1.NormalizedPrice").MULTIPLY().Column("p1.NormalizedPrice").ADD()  

                    .Column("p1.NormalizedStock").MULTIPLY().Column("p1.NormalizedStock")  

                )).MULTIPLY()  

                .SqlFunction(new SQRT(new ColumnArithmatic().Column("p2.NormalizedPrice").MULTIPLY().Column("p2.NormalizedPrice")  

                    .ADD().Column("p2.NormalizedStock").MULTIPLY().Column("p2.NormalizedStock")  

            .EndBracket()  

            ))  

        , "SimilarityValue")  

        .From("ProductData", "p1")  

        .Join(new List<IJoin>()  

        {  

            new CROSSJOIN().TableName(new Table("ProductData","p2"))  

        })  

        .Where(new Where(new Column("p1.ProductID"), SQLComparisonOperators.LESS_THAN, new Column("p2.ProductID")))  

    )  

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

        .Select()  

        .Columns("Product1", "ProductName1", "Product2", "ProductName2", "SimilarityValue")  

        .Column(new CASE()  

            .When(new Column("SimilarityValue").GreaterThan(1))  

            .Then(1)  

            .When(new Column("SimilarityValue").LessThan(-1))  

            .Then(-1)  

            .Else(new Column("SimilarityValue"))  

        , "AdjustedSimilarityValue")  

        .Column(new ACOS(new CASE()  

            .When(new Column("SimilarityValue").GreaterThan(1))  

            .Then(1)  

            .When(new Column("SimilarityValue").LessThan(-1))  

            .Then(-1)  

            .Else(new Column("SimilarityValue"))  

        )  

        , "SimilarityAngleInRadians")  

        .From("ProductPairs")  

    )  

    .Select().Top(10)  

    .Columns("Product1", "ProductName1", "Product2", "ProductName2", "SimilarityAngleInRadians")  

    .Column(new DEGREES(new Column("SimilarityAngleInRadians")), "SimilarityAngleInDegrees")  

    .From("AcosResult")  

    .OrderBy(new OrderBy().Set(new Column("SimilarityAngleInRadians")))  

    .Build();

Query build by SqlQueryBuilder 2

            
WITH ProductData
AS (SELECT ProductID,
           ProductName,
           UnitPrice,
           UnitsInStock,
           CAST (UnitPrice AS DECIMAL (10, 4)) / (SELECT MAX(UnitPrice) AS NormalizedPrice
                                                  FROM Products) AS NormalizedPrice,
           CAST (UnitsInStock AS DECIMAL (10, 4)) / (SELECT CAST (MAX(UnitsInStock) AS DECIMAL (10, 4)) AS NormalizedStock
                                                     FROM Products) AS NormalizedStock
    FROM Products
    WHERE UnitsInStock > @pMAIN_2507200155145894910
          AND UnitPrice > @pMAIN_2507200155145894911),
 ProductPairs
AS (SELECT p1.ProductID AS Product1,
           p1.ProductName AS ProductName1,
           p2.ProductID AS Product2,
           p2.ProductName AS ProductName2,
           (p1.NormalizedPrice * p2.NormalizedPrice + p1.NormalizedStock * p2.NormalizedStock) / (SQRT(p1.NormalizedPrice * p1.NormalizedPrice + p1.NormalizedStock * p1.NormalizedStock) * SQRT(p2.NormalizedPrice * p2.NormalizedPrice + p2.NormalizedStock * p2.NormalizedStock)) AS SimilarityValue
    FROM ProductData AS p1 CROSS JOIN ProductData AS p2
    WHERE p1.ProductID < p2.ProductID),
 AcosResult
AS (SELECT Product1,
           ProductName1,
           Product2,
           ProductName2,
           SimilarityValue,
           CASE WHEN SimilarityValue > @pMAIN_2507200155145894912 THEN @pMAIN_2507200155145894913 WHEN SimilarityValue < @pMAIN_2507200155145894914 THEN @pMAIN_2507200155145894915 ELSE SimilarityValue END AS AdjustedSimilarityValue,
           ACOS(CASE WHEN SimilarityValue > @pMAIN_2507200155145894916 THEN @pMAIN_2507200155145894917 WHEN SimilarityValue < @pMAIN_2507200155145894918 THEN @pMAIN_2507200155145894919 ELSE SimilarityValue END) AS SimilarityAngleInRadians
    FROM ProductPairs)
SELECT TOP 10 Product1,
              ProductName1,
              Product2,
              ProductName2,
              SimilarityAngleInRadians,
              DEGREES(SimilarityAngleInRadians) AS SimilarityAngleInDegrees
FROM AcosResult
ORDER BY SimilarityAngleInRadians ASC;

Parameters (If used)

Name Value
@pMAIN_2507200155145894910 0
@pMAIN_2507200155145894911 0
@pMAIN_2507200155145894912 1
@pMAIN_2507200155145894913 1
@pMAIN_2507200155145894914 -1
@pMAIN_2507200155145894915 -1
@pMAIN_2507200155145894916 1
@pMAIN_2507200155145894917 1
@pMAIN_2507200155145894918 -1
@pMAIN_2507200155145894919 -1

Query Results 2:

  Product1 ProductName1 Product2 ProductName2 SimilarityAngleInRadians SimilarityAngleInDegrees
1 10 Ikura 70 Outback Lager 0 0
2 13 Konbu 50 Valkoinen suklaa 0 0
3 25 NuNuCa Nuß-Nougat-Creme 52 Filo Mix 0 0
4 7 Uncle Bob's Organic Dried Pears 49 Maxilaku 0.000000021073424255447 0.00000120741826972573
5 34 Sasquatch Ale 46 Spegesild 0.0000896516979850368 0.00513666392072411
6 6 Grandma's Boysenberry Spread 55 Pâté chinois 0.000170212591084587 0.00975246308913292
7 26 Gumbär Gummibärchen 68 Scottish Longbreads 0.00032007904335472 0.0183391782948104
8 23 Tunnbröd 73 Röd Kaviar 0.000459719716187663 0.0263399994965051
9 40 Boston Crab Meat 73 Röd Kaviar 0.000509145312503459 0.0291718775653176
10 23 Tunnbröd 40 Boston Crab Meat 0.000968865028495687 0.0555118770506251

3. Usage of MAX to customer suppliers

SQL Server Query 3

            
WITH CustomerProductCounts AS (  

                SELECT  

                    c.CustomerID,  

                    c.CompanyName AS CustomerName,  

                    COUNT(DISTINCT od.ProductID) AS CustomerProductCount,  

                    CAST(COUNT(DISTINCT od.ProductID) AS DECIMAL(10, 8)) / (SELECT MAX(sub.ProductCount) from (SELECT  COUNT(DISTINCT od.ProductID) as ProductCount from Customers c join Orders o on c.CustomerID = o.CustomerID join [Order Details] od on o.OrderID = od.OrderID GROUP BY c.CustomerID) as sub) AS NormalizedCustomerProductCount  

                FROM Customers c  

                JOIN Orders o ON c.CustomerID = o.CustomerID  

                JOIN [Order Details] od ON o.OrderID = od.OrderID  

                GROUP BY c.CustomerID, c.CompanyName  

            ),  

            SupplierProductCounts AS (  

                SELECT  

                    s.SupplierID,  

                    s.CompanyName AS SupplierName,  

                    COUNT(DISTINCT p.ProductID) AS SupplierProductCount,  

                     CAST(COUNT(DISTINCT p.ProductID) AS DECIMAL(10, 8)) / (SELECT MAX(SupplierProductCount) FROM (SELECT s.SupplierID, COUNT(DISTINCT p.ProductID) AS SupplierProductCount from Suppliers s JOIN Products p ON s.SupplierID = p.SupplierID GROUP BY s.SupplierID) as sub2) AS NormalizedSupplierProductCount  

                FROM Suppliers s  

                JOIN Products p ON s.SupplierID = p.SupplierID  

                GROUP BY s.SupplierID, s.CompanyName  

            ),  

            CustomerSupplierPairs AS (  

                SELECT  

                    cp.CustomerID,  

                    cp.CustomerName,  

                    sp.SupplierID,  

                    sp.SupplierName,  

                    -- Calculate a similarity value  

                    (cp.NormalizedCustomerProductCount * sp.NormalizedSupplierProductCount) AS SimilarityValue  

                FROM CustomerProductCounts cp  

                CROSS JOIN SupplierProductCounts sp  

                WHERE cp.NormalizedCustomerProductCount > 0 AND sp.NormalizedSupplierProductCount > 0  

            ),  

            AcosResult as (  

                SELECT  

                    CustomerID,  

                    CustomerName,  

                    SupplierID,  

                    SupplierName,  

                    SimilarityValue,  

                    CASE  

                        WHEN SimilarityValue > 1 THEN 1  

                        WHEN SimilarityValue < -1 THEN -1  

                        ELSE SimilarityValue  

                    END AS AdjustedSimilarityValue,  

                    ACOS(  

                    CASE  

                        WHEN SimilarityValue > 1 THEN 1  

                        WHEN SimilarityValue < -1 THEN -1  

                        ELSE SimilarityValue  

                    END  

                    ) AS RelationshipAngleInRadians  

                FROM CustomerSupplierPairs  

            )  

            SELECT TOP 10  

                CustomerID,  

                CustomerName,  

                SupplierID,  

                SupplierName,  

                RelationshipAngleInRadians,  

                DEGREES(RelationshipAngleInRadians) AS RelationshipAngleInDegrees  

            FROM AcosResult  

            ORDER BY RelationshipAngleInRadians;

Create SQL query with SqlQueryBuilder 3

            
var (sql3, parameters3) = new SqlQueryBuilder()  

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

        .Select()  

        .Column("c.CustomerID", "CustomerID")  

        .Column("c.CompanyName", "CustomerName")  

        .Column(new COUNT(new Column("od.ProductID"), true), "CustomerProductCount")  

        .Column(new ColumnArithmatic(new CAST(new COUNT(new Column("od.ProductID"), true), SqlDataType.DECIMAL, new Tuple<int, int>(10, 8)))  

            .DIVIDE(new SqlQueryBuilder()  

                .Select()  

                .Column(new MAX(new Column("sub.ProductCount")), "NormalizedCustomerProductCount")  

                .From(new SqlQueryBuilder()  

                    .Select()  

                    .Column(new COUNT(new Column("od.ProductID"), true), "ProductCount")  

                    .From("Customers", "c")  

                    .Join(new List<IJoin>()  

                    {  

                        new INNERJOIN().TableName(new Table("Orders","o"))  

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

                        new INNERJOIN().TableName(new Table("[Order Details]","od"))  

                            .On(new Column("o.OrderID").Equale(new Column("od.OrderID")))  

                    })  

                    .GroupBy(new GroupBy("c.CustomerID"))  

                , "sub")  

            )  

        , "NormalizedCustomerProductCount")  

        .From("Customers", "c")  

        .Join(new List<IJoin>()  

        {  

            new INNERJOIN().TableName(new Table("Orders","o"))  

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

            new INNERJOIN().TableName(new Table("[Order Details]","od"))  

                .On(new Column("o.OrderID").Equale(new Column("od.OrderID")))  

        })  

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

    )  

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

        .Select()  

        .Column("s.SupplierID", "SupplierID")  

        .Column("s.CompanyName", "SupplierName")  

        .Column(new COUNT(new Column("p.ProductID"), true), "SupplierProductCount")  

        .Column(new ColumnArithmatic(new CAST(new COUNT(new Column("p.ProductID"), true), SqlDataType.DECIMAL, new Tuple<int, int>(10, 8)))  

            .DIVIDE(new SqlQueryBuilder().Select()  

                .Column(new MAX(new Column("SupplierProductCount")), "NormalizedSupplierProductCount")  

                .From(new SqlQueryBuilder()  

                    .Select()  

                    .Column("s.SupplierID", "SupplierID")  

                    .Column(new COUNT(new Column("p.ProductID"), true), "SupplierProductCount")  

                    .From("Suppliers", "s")  

                    .Join(new List<IJoin>()  

                    {  

                        new INNERJOIN().TableName(new Table("Products","p"))  

                            .On(new Column("s.SupplierID").Equale(new Column("p.SupplierID")))  

                    })  

                    .GroupBy(new GroupBy("s.SupplierID"))  

                , "sub2")  

            )  

        , "NormalizedSupplierProductCount")  

        .From("Suppliers", "s")  

        .Join(new List<IJoin>()  

        {  

            new INNERJOIN().TableName(new Table("Products","p"))  

                .On(new Column("s.SupplierID").Equale(new Column("p.SupplierID")))  

        })  

        .GroupBy(new GroupBy("s.SupplierID", "s.CompanyName"))  

    )  

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

        .Select()  

        .Columns("cp.CustomerID", "cp.CustomerName", "sp.SupplierID", "sp.SupplierName")  

        .Column(new ColumnArithmatic().StartBracket("cp.NormalizedCustomerProductCount")  

            .MULTIPLY("sp.NormalizedSupplierProductCount").EndBracket()  

        , "SimilarityValue")  

        .From("CustomerProductCounts", "cp")  

        .Join(new List<IJoin>()  

        {  

            new CROSSJOIN().TableName(new Table("SupplierProductCounts","sp"))  

        })  

        .Where(new Where(new Column("cp.NormalizedCustomerProductCount").GreaterThan(0))  

            .AND(new Column("sp.NormalizedSupplierProductCount").GreaterThan(0)))  

    )  

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

        .Select()  

        .Columns("CustomerID", "CustomerName", "SupplierID", "SupplierName", "SimilarityValue")  

        .Column(new CASE()  

            .When(new Column("SimilarityValue").GreaterThan(1))  

            .Then(1)  

            .When(new Column("SimilarityValue").LessThan(-1))  

            .Then(-1)  

            .Else(new Column("SimilarityValue"))  

        , "AdjustedSimilarityValue")  

        .Column(new ACOS(new CASE()  

            .When(new Column("SimilarityValue").GreaterThan(1))  

            .Then(1)  

            .When(new Column("SimilarityValue").LessThan(-1))  

            .Then(-1)  

            .Else(new Column("SimilarityValue")))  

        , "RelationshipAngleInRadians")  

        .From("CustomerSupplierPairs")  

    )  

    .Select().Top(10)  

    .Columns("CustomerID", "CustomerName", "SupplierID", "SupplierName", "RelationshipAngleInRadians")  

    .Column(new DEGREES(new Column("RelationshipAngleInRadians")), "RelationshipAngleInDegrees")  

    .From("AcosResult")  

    .OrderBy(new OrderBy().SetColumnAscending("RelationshipAngleInRadians"))  

    .Build();

Query build by SqlQueryBuilder 3

            
WITH CustomerProductCounts
AS (SELECT c.CustomerID AS CustomerID,
           c.CompanyName AS CustomerName,
           COUNT(DISTINCT od.ProductID) AS CustomerProductCount,
           CAST (COUNT(DISTINCT od.ProductID) AS DECIMAL (10, 8)) / (SELECT MAX(sub.ProductCount) AS NormalizedCustomerProductCount
                                                                     FROM (SELECT COUNT(DISTINCT od.ProductID) AS ProductCount
                                                                           FROM Customers AS c
                                                                                INNER JOIN
                                                                                Orders AS o
                                                                                ON c.CustomerID = o.CustomerID
                                                                                INNER JOIN
                                                                                [Order Details] AS od
                                                                                ON o.OrderID = od.OrderID
                                                                           GROUP BY c.CustomerID) AS sub) AS NormalizedCustomerProductCount
    FROM Customers AS c
         INNER JOIN
         Orders AS o
         ON c.CustomerID = o.CustomerID
         INNER JOIN
         [Order Details] AS od
         ON o.OrderID = od.OrderID
    GROUP BY c.CustomerID, c.CompanyName),
 SupplierProductCounts
AS (SELECT s.SupplierID AS SupplierID,
           s.CompanyName AS SupplierName,
           COUNT(DISTINCT p.ProductID) AS SupplierProductCount,
           CAST (COUNT(DISTINCT p.ProductID) AS DECIMAL (10, 8)) / (SELECT MAX(SupplierProductCount) AS NormalizedSupplierProductCount
                                                                    FROM (SELECT s.SupplierID AS SupplierID,
                                                                                 COUNT(DISTINCT p.ProductID) AS SupplierProductCount
                                                                          FROM Suppliers AS s
                                                                               INNER JOIN
                                                                               Products AS p
                                                                               ON s.SupplierID = p.SupplierID
                                                                          GROUP BY s.SupplierID) AS sub2) AS NormalizedSupplierProductCount
    FROM Suppliers AS s
         INNER JOIN
         Products AS p
         ON s.SupplierID = p.SupplierID
    GROUP BY s.SupplierID, s.CompanyName),
 CustomerSupplierPairs
AS (SELECT cp.CustomerID,
           cp.CustomerName,
           sp.SupplierID,
           sp.SupplierName,
           (cp.NormalizedCustomerProductCount * sp.NormalizedSupplierProductCount) AS SimilarityValue
    FROM CustomerProductCounts AS cp CROSS JOIN SupplierProductCounts AS sp
    WHERE cp.NormalizedCustomerProductCount > @pMAIN_2507200155146503070
          AND sp.NormalizedSupplierProductCount > @pMAIN_2507200155146503071),
 AcosResult
AS (SELECT CustomerID,
           CustomerName,
           SupplierID,
           SupplierName,
           SimilarityValue,
           CASE WHEN SimilarityValue > @pMAIN_2507200155146503072 THEN @pMAIN_2507200155146503073 WHEN SimilarityValue < @pMAIN_2507200155146503074 THEN @pMAIN_2507200155146503075 ELSE SimilarityValue END AS AdjustedSimilarityValue,
           ACOS(CASE WHEN SimilarityValue > @pMAIN_2507200155146503076 THEN @pMAIN_2507200155146503077 WHEN SimilarityValue < @pMAIN_2507200155146503078 THEN @pMAIN_2507200155146503079 ELSE SimilarityValue END) AS RelationshipAngleInRadians
    FROM CustomerSupplierPairs)
SELECT TOP 10 CustomerID,
              CustomerName,
              SupplierID,
              SupplierName,
              RelationshipAngleInRadians,
              DEGREES(RelationshipAngleInRadians) AS RelationshipAngleInDegrees
FROM AcosResult
ORDER BY RelationshipAngleInRadians ASC;

Parameters (If used)

Name Value
@pMAIN_2507200155146503070 0
@pMAIN_2507200155146503071 0
@pMAIN_2507200155146503072 1
@pMAIN_2507200155146503073 1
@pMAIN_2507200155146503074 -1
@pMAIN_2507200155146503075 -1
@pMAIN_2507200155146503076 1
@pMAIN_2507200155146503077 1
@pMAIN_2507200155146503078 -1
@pMAIN_2507200155146503079 -1

Query Results 3:

  CustomerID CustomerName SupplierId SupplierName RelationshipAngleInRadians RelationshipAngleInDegrees
1 ERNSH Ernst Handel 7 Pavlova, Ltd. 0 0
2 ERNSH Ernst Handel 12 Plutzer Lebensmittelgroßmärkte AG 0 0
3 SAVEA Save-a-lot Markets 7 Pavlova, Ltd. 0.328806015570302 18.8391969706911
4 SAVEA Save-a-lot Markets 12 Plutzer Lebensmittelgroßmärkte AG 0.328806015570302 18.8391969706911
5 QUICK QUICK-Stop 7 Pavlova, Ltd. 0.505360510284157 28.9550243718598
6 QUICK QUICK-Stop 12 Plutzer Lebensmittelgroßmärkte AG 0.505360510284157 28.9550243718598
7 RATTC Rattlesnake Canyon Grocery 7 Pavlova, Ltd. 0.637524882148117 36.5274850816623
8 RATTC Rattlesnake Canyon Grocery 12 Plutzer Lebensmittelgroßmärkte AG 0.637524882148117 36.5274850816623
9 ERNSH Ernst Handel 2 New Orleans Cajun Delights 0.643501108793284 36.869897645844
10 ERNSH Ernst Handel 8 Specialty Biscuits, Ltd. 0.643501108793284 36.869897645844