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
if i want customers who bought similar items like customer ‘a’
the query should return
|customer_id||similar items count|
‘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
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)