SQL Query - How to find most frequently purchased items together

Practice data

CREATE TABLE CustOrders (

Orderid INTEGER NULL,

Customerid INTEGER NULL,

Productid Varchar(40) NULL

);

Insert into CustOrders VALUES (111, 1, 'AAA');

Insert into CustOrders VALUES (111, 1,'BBB');

Insert into CustOrders VALUES (2222, 2,'CCC');

Insert into CustOrders VALUES (2222, 2,'AAA');

Insert into CustOrders VALUES (2222, 2,'DDD');

Insert into CustOrders VALUES (3333, 3,'BBB');

Insert into CustOrders VALUES (3333, 3,'AAA');

Insert into CustOrders VALUES (3333, 3,'HHH');

Insert into CustOrders VALUES (4444, 4,'AAA');

Insert into CustOrders VALUES (4444, 4,'BBB');

Insert into CustOrders VALUES (4444, 4,'CCC');

SQL Query

Select Top 2 O1.ProductID, O2.ProductID

,Count(*) as PurchaseFrequency

from CustOrders O1

INNER JOIN CustOrders O2

ON O1.CustomerID = O2.CustomerID

AND O1.OrderID = O2.OrderID

AND O1.ProductID < O2.ProductID

Group By O1.ProductID, O2.ProductID

Order By PurchaseFrequency DESC

Learn More

For any questions, reach out to learn@knowstar.org.

Consider expanding your expertise with these certifications:

Google Data Analytics Professional Certificate: https://imp.i384100.net/OR37oQ
Google Advanced Data Analytics Professional Certificate: https://imp.i384100.net/eK1WmQ