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

Issue

customers table

purchase table

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

‘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)