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

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

Answer

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.