Data Requirements

Preparing the data for analysis

• A query table that contains all possible pairs of combinations that could occur together.
• A query table to get the number of transactions for combinations that have appeared together or combinations that has been purchased.
• As a final step, merge the all possible combinations query table and the number of transactions query table.

Generating all Possible Pairs of Combinations

1. Perform a self-join operation on the Items Name column of the base table, Orders. The self-join operation compares each row (transaction) with every other row (transaction).
   
2. Use the condition Greater than (>) to ensure that each pair of items is considered only once and avoid duplicates where the same pair appears in reverse order, like (Lavender Candle, British Rose) or (British Rose, Lavender Candle) or (Lavender Candle, Lavender Candle) 
   

   SELECT     "Orders" . "Item Name" "Item",     "Orders copy". "Item Name" "Item Copy" FROM ( SELECT `orders` .`Item Name` "Item Name"               FROM  "Orders"                 GROUP BY  1  ) AS  "Orders" JOIN(SELECT `orders`.`Item Name` "Item Name" FROM  "Orders"  GROUP BY  1  ) AS  "Orders copy" ON "Orders" . "Item Name"  > "Orders copy" . "Item Name"

Distinct Count for Existing Combination

1. Perform a self-join operation on the Items Name column of the base table, Bill Items. The self-join operation compares each row (transaction) with every other row (transaction).
   
2. Use the count_distinct function on the Bill Number column to get the total number of times a combination has occurred together.

SELECT "Orders" . "Item Name" "Item", "Orders Copy" . "Items Name" "Item Combo", count_distinct ("Orders Copy" . "Bill Number") "Number of Transactions" FROM  "Orders" JOIN( SELECT `Orders`.`Item Name` "Items name", `Orders`.`Bill Number` "BillNo" FROM  "Orders"  ) AS  "Orders Copy"  ON  "Orders" . "Bill Number"  = "Orders Copy".  "Bill Number" AND "Orders" . "Item Name"  > "Orders Copy" ." Items Name"   GROUP BY 1,   2

Market Basket Analysis for 3 - item Combination