Frequently Purchased Pairs

Back to questions

Medium

Walmart

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 Name	Type
transaction_id	integer
product_id	integer
user_id	integer
transaction_date	datetime

Example Input:

transaction_id	product_id	user_id	transaction_date
231574	111	234	03/01/2022 12:00:00
231574	444	234	03/01/2022 12:00:00
231574	222	234	03/01/2022 12:00:00
137124	111	125	03/05/2022 12:00:00
137124	444	125	03/05/2022 12:00:00

Table:

Column Name	Type
product_id	integer
product_name	string

Example Input:

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

Example Output:

product1	product2	combo_num
banana	apple	2
banana	soy milk	1
soy milk	apple	1

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.

Solution

We need information on the most frequently purchased products. Let's start small by joining the and tables together based on the related field .

Showing the first 5 rows of output:

user_id	product_id	transaction_id	product_name
234	111	231574	apple
234	444	231574	banana
234	222	231574	soy milk
125	444	137124	banana
311	222	256234	soy milk

Using the query above, we create a CTE and perform a SELF JOIN to fetch products that were purchased together by a single user by joining on .

Note that we want all pairs of the products, but we do not want to overcount. For example, if user A purchased apple and instant oatmeal in the same transaction, then we only want to count the (apple, instant oatmeal) transaction once, and not also (instant oatmeal, apple).

To avoid double-counting, we can use a condition within the SELF JOIN that the product_id of A is less than that of product B. Lastly, we use a clause for each pair of products.

Output:

product1	product2	combo_num
banana	apple	2
banana	soy milk	2
instant oatmeal	soy milk	1
soy milk	apple	1

Did you see how there is no overcounting of the different combination of the same products?

Lastly, we use a clause for each pair of products and sort the resulting count by descending, taking the top 3 using clause:

Results:

product1	product2	combo_num
banana	apple	2
banana	soy milk	2
instant oatmeal	soy milk	1

Solution:

PostgreSQL 14

Refer friends to get bonus content & cool prizes.