So you want to find popular combinations of products?
I'd try the following approach, and see where it leads you. Make a join from this table to itself, linked by customer. Require that the id for the product in the second copy of the table is higher than the one in the first copy. That way you'll eliminate links of products to themselves, as well as removing duplicates a->b and b->a. See what products are still very popular, and what combinations come out a lot.
From there, it's not to hard to identify those customers that bought both (or all three...) products.
Roughly tested SQL:
SELECT a.product as product_a, a.customer, b.product as product_b
FROM Sales a INNER JOIN Sales b ON a.Customer = b.Customer
WHERE a.product < b.product
and this variation:
SELECT a.product as product_a, b.product as product_b, count(*) as pop
+ularity
FROM Sales a INNER JOIN Sales b ON a.Customer = b.Customer WHERE a.pro
+duct < b.product
GROUP BY a.product, b.product
|