[SOLVED] mysql return people with similar purchase and desc order by count of similar items

Issue

customers table

id name
1 a
2 b
3 c
4 d

purchase table

product_id customer_id
x 1
y 1
x 4
y 4
x 3
z 2

the customer table has customer data and purchase table has order data.
Now coming to question, I want customers id who bought similar products ordered by the count of similar items
eg:
if i want customers who bought similar items like customer ‘a’
the query should return

customer_id similar items count
4 2
3 1

‘a’ bought x,y
d’ bought x,y,
c’ bought x

so d and c should be returned order by similar items count (desc)

i am not good at larger sql queries, so i need to ask this.

Thank you in advance

Solution

SELECT t1.customer_id, t2.customer_id, COUNT(*) cnt
FROM purchase t1
JOIN purchase t2 ON t1.product_id = t2.product_id
                AND t1.customer_id < t2.customer_id
GROUP BY t1.customer_id, t2.customer_id;

will return customers pair and the amount of similar products for them.

Answered By – Akina

Answer Checked By – Pedro (BugsFixing Volunteer)

Leave a Reply

Your email address will not be published.