logo

Back to questions

Frequently Purchased Pairs [Walmart SQL Interview Question]

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 number of unique product combinations that are bought together (purchased in the same transaction).

For example, if I find two transactions where apples and bananas are bought, and another transaction where bananas and soy milk are bought, my output would be 2 to represent the 2 unique combinations. Your output should be a single number.

Assumption:

  • For each transaction, a maximum of 2 products is purchased.

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:

combo_num
4

There are 4 unique purchase combinations present in the example data.

The dataset you are querying against may have different input & output - this is just an example!

PostgreSQL 14