logo

Back to questions

Frequently Purchased Pairs

Medium

This is the same question as problem #30 in the SQL Chapter of Ace the Data Science Interview!

Assume you are given the following tables on Walmart transactions and products. Find the top 3 products that are most frequently bought together (purchased in the same transaction).

Output the name of product #1, name of product #2 and number of combinations in descending order.

Table:

Column NameType
transaction_idinteger
product_idinteger
user_idinteger
transaction_datedatetime

Example Input:

transaction_idproduct_iduser_idtransaction_date
23157411123403/01/2022 12:00:00
23157444423403/01/2022 12:00:00
23157422223403/01/2022 12:00:00
13712411112503/05/2022 12:00:00
13712444412503/05/2022 12:00:00

Table:

Column NameType
product_idinteger
product_namestring

Example Input:

product_idproduct_name
111apple
222soy milk
333instant oatmeal
444banana
555chia seed

Example Output:

product1product2combo_num
bananaapple2
bananasoy milk1
soy milkapple1

Explanation

Banana and apple are both present in two transactions (231574 and 137124), so they're at the top of the results with a of 2.

PostgreSQL 14

Refer friends to get bonus content & cool prizes.