Interview Query

Paired Products

25
Have you seen this question before?

Let’s say we have two tables, transactions and products. Hypothetically the transactions table consists of over a billion rows of purchases bought by users.

We are trying to find paired products that are often purchased together by the same user, such as wine and bottle openers, chips and beer, etc..

Write a query to find the top five paired products and their names.

Notes: For the purposes of satisfying the test case, P1 should be the item that comes first in the alphabet.

Example:

Input:

transactions table

Column Type
id INTEGER
user_id INTEGER
created_at DATETIME
product_id INTEGER
quantity INTEGER

products table

Column Type
id INTEGER
name VARCHAR
price FLOAT

Output:

Column Type
P1 VARCHAR
P2 VARCHAR
count INTEGER
Next question: Like Tracker
.....
MySQL 8.0.17
Loading editor
Use Shift + Enter to run query