Home > Software engineering >  mysql return people with similar purchase and desc order by count of similar items
mysql return people with similar purchase and desc order by count of similar items

Time:03-25

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

CodePudding user response:

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.

  • Related