How to Conduct SKU Affinity Analysis (product basket analysis) via MS SQL Server?

Background: There are a lot of key factors for the product stocking decision. When it comes to how to select products to be stocked in the assortment, the first thought is to prioritize either high demand or high profitable SKUs. However, there may be some low-profitable SKUs (even negative margin) ordered with very profitable SKUs together frequently, which means they won’t be picked if the logic is to select products at the descending order of margin.

Purpose: We are writing SQL queries (See here if you are interested in Python version) to transform the order-level table into product-pair-level table so that we will know how frequent each SKU combinations was placed by customers.

Raw Data: [Simulation].[006_Order] (112913 order-sku lines)

Steps:

1. Create product-pair-level view.

Create View [Simulation].[006_Order-SKU-Pair]
as
SELECT P1.[Order], P1.Sku AS Sku1, P2.Sku AS SKU2 
FROM 
(
SELECT [Order] ,[Sku]
FROM [Simulation].[006_Order]
) AS P1
INNER JOIN
(
SELECT [Order] ,[Sku]
FROM [Simulation].[006_Order]
) AS P2
ON 
P1.[Order] = P2.[Order]
WHERE P2.Sku > P1.Sku;

2. Calculate frequency of each combination.

SELECT A.Sku1, A.SKU2, COUNT(*) AS Frequency…