t, Sql Server - rextester

Language: Layout:

CREATE TABLE Orders
    ([OrderID] int, [OtherLangDescription] varchar(20))
;
    
INSERT INTO Orders
    ([OrderID], [OtherLangDescription])
VALUES
    (137413, 'frying Potato'),
    (137413, 'Flame Grape'),
    (137413, 'Zebdaya Mango'),
    (137413, 'Green pepper'),
    (137413, 'Molokhia'),
    (137413, 'White Eggplant'),
    (137413, 'Golden Onion'),
    (137413, 'Zucchini'),
    (137413, 'Dill'),
    (137413, 'Parsley'),
    (137413, 'Coriander'),
    (137413, 'Local Eggplant'),
    (136729, 'Local Lemon'),
    (136729, 'Local Pear'),
    (136729, 'Flame Grape'),
    (136729, 'Superior Grape'),
    (136729, 'Capsicum'),
    (136729, 'Green pepper'),
    (136729, 'Cucumber'),
    (136729, 'White Eggplant'),
    (136729, 'Carrot'),
    (136729, 'Tomato'),
    (136729, 'Zucchini'),
    (136729, 'Parsley'),
    (136729, 'Local Celery'),
    (137260, 'frying Potato'),
    (137260, 'Flame Grape'),
    (137260, 'Superior Grape'),
    (137260, 'Momtaza Owais Mango'),
    (137260, 'Barshomi Figs'),
    (137260, 'Green pepper'),
    (137260, 'Cucumber'),
    (137260, 'Tomato'),
    (137260, 'Red Onion'),
    (137260, 'Parsley'),
    (137429, 'Flame Grape'),
    (137429, 'Momtaza Owais Mango'),
    (137429, 'Barshomi Figs'),
    (137429, 'Hot Pepper'),
    (137429, 'Green pepper'),
    (137429, 'Cucumber'),
    (137429, 'Red Onion'),
    (137429, 'Zucchini'),
    (137429, 'Parsley'),
    (137902, 'Local Pear'),
    (137902, 'Superior Grape'),
    (137902, 'Momtaza Owais Mango'),
    (137902, 'Barshomi Figs'),
    (137902, 'Cucumber'),
    (137902, 'White Eggplant'),
    (137902, 'Tomato'),
    (137974, 'Momtaza Owais Mango'),
    (137974, 'Barshomi Figs'),
    (138837, 'Classic Eggplant'),
    (138837, 'Local Pear'),
    (138837, 'Tomato'),
    (138837, 'Dill'),
    (138837, 'Parsley'),
    (138837, 'Coriander'),
    (139424, 'Classic Eggplant'),
    (139424, 'frying Potato'),
    (139424, 'Local Pear'),
    (139424, 'Chili Pepper'),
    (139424, 'Green pepper'),
    (139424, 'White Eggplant'),
    (139424, 'Black Eggplant'),
    (139424, 'Tomato'),
    (139424, 'Zaghlol Dates'),
    (139642, 'Local Lemon'),
    (139642, 'Classic Eggplant'),
    (139642, 'frying Potato'),
    (139642, 'Banati Grape'),
    (139642, 'Chili Pepper'),
    (139642, 'Green pepper'),
    (139642, 'Tomato'),
    (140676, 'Local Lemon'),
    (140676, 'Classic Eggplant'),
    (140676, 'Banati Grape'),
    (140676, 'Chili Pepper'),
    (140676, 'Green pepper'),
    (140676, 'Tomato'),
    (140938, 'Classic Eggplant'),
    (140938, 'frying Potato'),
    (140938, 'Flame Grape'),
    (140938, 'Cucumber'),
    (140938, 'Molokhia'),
    (140938, 'Cooking Potato'),
    (140938, 'Tomato'),
    (140938, 'Cantaloupe'),
    (140938, 'Green Beans'),
    (140938, 'Baladi Cabbage'),
    (141302, 'Local Lemon'),
    (141302, 'Baladi Garlic'),
    (141302, 'Zucchini'),
    (141302, 'Dill'),
    (141302, 'Parsley'),
    (141302, 'Red Globe Grape'),
    (137302, 'Melon'),
    (137302, 'Sadeeka Mango'),
    (137302, 'Barshomi Figs'),
    (137302, 'Avocado'),
    (138297, 'frying Potato'),
    (138297, 'Local Pear'),
    (138297, 'Flame Grape'),
    (138297, 'Fons Mango'),
    (138297, 'Barshomi Figs'),
    (138297, 'Cucumber'),
    (138297, 'Carrot'),
    (138297, 'Tomato'),
    (138297, 'Red Onion'),
    (138297, 'Dill'),
    (138297, 'Parsley'),
    (138297, 'Coriander'),
    (138297, 'Owais Mango'),
    (134444, 'Strawberry'),
    (134444, 'Sugary Peach'),
    (134444, 'Local Cucumber'),
    (141134, 'Imported Peach'),
    (141134, 'Guava'),
    (141134, 'Watermelon ( per KG)'),
    (141134, 'Syrian plum'),
    (134468, 'Watermelon ( per KG)'),
    (134468, 'Hot Pepper'),
    (131965, 'Watermelon ( per KG)'),
    (131965, 'Hot Pepper'),
    (131965, 'Cucumber'),
    (131965, 'White Eggplant'),
    (131965, 'Grape Leaves'),
    (131965, 'Carrot'),
    (131965, 'Tomato'),
    (131965, 'Red Onion'),
    (131965, 'Sugary Peach'),
    (135722, 'Local Lemon'),
    (135722, 'Mushroom (200G) old'),
    (135722, 'Red Radish old'),
    (135722, 'Orange For Juice'),
    (135722, 'Watermelon ( per KG)'),
    (135722, 'Melon'),
    (135722, 'Flame Grape'),
    (135722, 'Chili Pepper'),
    (135722, 'Capsicum'),
    (135722, 'Carrot'),
    (135722, 'Tomato'),
    (135722, 'Broccoli'),
    (135722, 'Sugary Peach'),
    (135722, 'Local Cucumber'),
    (139444, 'Classic Eggplant'),
    (139444, 'frying Potato'),
    (139444, 'Capsicum'),
    (139444, 'Green pepper'),
    (139444, 'Golden Onion'),
    (139444, 'Tomato'),
    (131866, 'Local Lemon'),
    (131866, 'Classic Eggplant'),
    (131866, 'Morket Tangerine'),
    (131866, 'Cooking Potato'),
    (131866, 'Red Onion'),
    (131866, 'Tamr hendi'),
    (131866, 'Deluxe Dried Dates'),
    (136078, 'Capsicum'),
    (136078, 'Green pepper'),
    (136078, 'Grape Leaves'),
    (136078, 'Cooking Potato'),
    (136078, 'Tomato'),
    (136078, 'Cantaloupe'),
    (136078, 'Local Cucumber'),
    (136078, 'Japanese Plum'),
    (136078, 'Dill'),
    (136078, 'White Cabbage'),
    (136078, 'Coriander'),
    (136078, 'Arugula'),
    (136078, 'Local Apple'),
    (137419, 'frying Potato'),
    (137419, 'Flame Grape'),
    (137419, 'Superior Grape'),
    (137419, 'Sadeeka Mango'),
    (137419, 'Capsicum'),
    (137419, 'Green pepper'),
    (137419, 'Black Eggplant'),
    (137419, 'Cooking Potato'),
    (137419, 'Carrot'),
    (137419, 'Tomato'),
    (137419, 'Local Cucumber'),
    (137419, 'Local Hot Pepper'),
    (139214, 'Sokkary Mango'),
    (139214, 'Nems Watermelon (KG)'),
    (139214, 'Sugary Peach'),
    (139214, 'Red Globe Grape'),
    (139214, 'Owais Mango'),
    (139214, 'Lebanese Apple'),
    (131997, 'Orange For Juice'),
    (131997, 'Watermelon ( per KG)'),
    (131997, 'Golden Apple'),
    (131997, 'Tomato'),
    (139482, 'Sweet Potato'),
    (139482, 'Sokkary Mango'),
    (139482, 'Gala Apple'),
    (139482, 'Tomato'),
    (139482, 'Okra'),
    (139482, 'Mint'),
    (139482, 'Red Globe Grape'),
    (136066, 'Watermelon ( per KG)'),
    (136066, 'Cooking Potato'),
    (136066, 'Taro'),
    (136066, 'Cantaloupe'),
    (136066, 'Local Cucumber'),
    (136066, 'Dried Dates'),
    (136066, 'Local Apple'),
    (136610, 'Local Lemon'),
    (136610, 'Flame Grape'),
    (136610, 'Superior Grape'),
    (136610, 'Parsley'),
    (136610, 'Local Apple'),
    (137352, 'Watermelon ( per KG)'),
    (137352, 'Flame Grape'),
    (137352, 'Hot Pepper'),
    (137352, 'Capsicum'),
    (137352, 'Tomato'),
    (137352, 'Local Cucumber'),
    (137352, 'Parsley'),
    (137352, 'Local Apple'),
    (133358, 'Watermelon ( per KG)'),
    (133358, 'Golden Onion'),
    (133358, 'Sugary Peach'),
    (134522, 'Arugula old'),
    (134522, 'frying Potato'),
    (134522, 'Parsle old'),
    (134522, 'Coriander old'),
    (134522, 'Dill old'),
    (134522, 'White Cabbage Old'),
    (134522, 'Tomato'),
    (134522, 'Local Cucumber'),
    (131481, 'Orange For Juice'),
    (131481, 'Watermelon ( per KG)'),
    (131481, 'Red Onion'),
    (131918, 'Watermelon ( per KG)'),
    (131918, 'Nectarine Peach'),
    (131918, 'Granny Apple'),
    (131918, 'Apricot'),
    (135758, 'Carrot'),
    (135758, 'Tomato'),
    (135758, 'Red Onion'),
    (135758, 'Zucchini'),
    (135678, 'Parsle old'),
    (135678, 'Coriander old'),
    (135678, 'Dill old'),
    (135678, 'Flame Grape'),
    (135678, 'Capsicum'),
    (135678, 'Carrot'),
    (135678, 'Tomato'),
    (135678, 'Local Cucumber'),
    (135678, 'Zucchini'),
    (131986, 'Orange For Juice'),
    (131986, 'Golden Apple'),
    (131986, 'Golden Onion'),
    (131986, 'Cooking Potato'),
    (131986, 'Carrot'),
    (131986, 'Tomato'),
    (131986, 'Taro'),
    (139468, 'Local Lemon'),
    (139468, 'Cauliflower'),
    (139468, 'Classic Eggplant'),
    (139468, 'frying Potato'),
    (139468, 'Autumn Royal Grape'),
    (139468, 'Capsicum'),
    (139468, 'Green pepper'),
    (139468, 'Cucumber'),
    (139468, 'Tomato'),
    (139468, 'Red Globe Grape'),
    (139533, 'Local Lemon'),
    (139533, 'Classic Eggplant'),
    (139533, 'frying Potato'),
    (139533, 'Autumn Royal Grape'),
    (139533, 'Capsicum'),
    (139533, 'Green pepper'),
    (139533, 'Cucumber'),
    (139533, 'Tomato'),
    (139533, 'Red Globe Grape'),
    (131950, 'Watermelon ( per KG)'),
    (137540, 'Local Lemon'),
    (137540, 'Classic Eggplant'),
    (137540, 'frying Potato'),
    (137540, 'Flame Grape'),
    (137540, 'Tomato'),
    (137540, 'Red Onion'),
    (137540, 'Local Cucumber'),
    (138021, 'Superior Grape'),
    (138021, 'Sokkary Mango'),
    (138021, 'Sadeeka Mango'),
    (138021, 'Barshomi Figs'),
    (138021, 'Hot Pepper'),
    (138021, 'Chili Pepper'),
    (138021, 'Black Eggplant'),
    (138021, 'Red Globe Grape'),
    (138021, 'Syrian plum'),
    (134490, 'Parsle old'),
    (134490, 'Coriander old'),
    (134490, 'Dill old'),
    (134490, 'White Cabbage Old'),
    (134490, 'Capsicum')
;

WITH products as (
    SELECT [OtherLangDescription], COUNT(*) as orders_total
    FROM Orders 
    GROUP BY [OtherLangDescription]
), correlation as (
    SELECT p1.[OtherLangDescription] as p1,
           p1.orders_total as total1,
           p2.[OtherLangDescription] as p2,
           p2.orders_total as total2    
    FROM products p1
    CROSS JOIN products p2
), dual_products as (
    SELECT p1, p2, COUNT (o1.OrderID) as pair_orders_total
    FROM correlation c
    JOIN orders o1
      ON c.p1 = o1.[OtherLangDescription]
    JOIN orders o2
      ON c.p2 = o2.[OtherLangDescription]
     AND o1.orderID = o2.orderID
    GROUP BY p1, p2
)
SELECT top 100 C.*, 
       d.pair_orders_total,
       d.pair_orders_total * 1.0 / c.total1 as correlation
FROM correlation c
JOIN dual_products d
  ON c.p1 = d.p1
 AND c.p2 = d.p2

View schema
Execution time: 0,09 sec, rows selected: 100, rows affected: 300, absolute service time: 0,31 sec