MySQL query - find products purchased by similar customers

aphrodite 0
Viewed 41

Suppose we have an 'order' table consisting of two fields - customer and product purchased by the customer.

CREATE TABLE orders (customer VARCHAR(16) NOT NULL, product_id INT NOT NULL);

If similar customers are defined as those who purchased at least two same products. I'm wondering how I can get products purchased by similar customers of each customer by a query. (Note that the products purchased by the customer itself should be excluded).

The input:

INSERT INTO orders VALUES 
  ('A', 1), ('A', 2), ('A', 3),
  ('B', 1), ('B', 2), ('B', 4), 
  ('C', 1), ('C', 3), ('C', 4), ('C', 5);

Ideal output:

|customer|product_id|
|---|---|
|'A'| 4 |
|'A'| 5 |
|'B'| 3 |
|'B'| 5 |
|'C'| 2 |

For example, customer A and customer B both bought product 1 and product 2, so they are similar customers. Therefore, product 4 is a product bought by a similar customer of customer A, so is it listed. I could get similar customers by the following code but don't know how to proceed:

WITH common AS (
SELECT o1.customer AS cust_1, o2.customer AS cust_2, o1.product_id AS prod_id,
       COUNT(*) OVER (PARTITION BY o1.customer, o2.customer) AS same_purchased
FROM orders o1 JOIN orders o2
ON (o1.customer < o2.customer AND o1.product_id = o2.product_id))
SELECT cust_1, cust_2, prod_id
FROM common WHERE same_purchased >= 2

SQL fiddle: https://dbfiddle.uk/S6X7gCJH

mysql
1 Answers

Here the SQL self-explanatory:

WITH similars as (
  SELECT 
    c1.customer, 
    c2.customer as similar 
  from 
    orders c1 inner join 
    -- join orders with itself
    orders c2 
      on 
         -- non equijoin, other customers:
         c1.customer != c2.customer 
     and c1.product_id = c2.product_id 
  group by 
    c1.customer, 
    c2.customer 
  having 
    -- at least two items in common:
    count(*) > 1
) 
select distinct 
  s.customer, 
  o.product_id -- products of my similar customers 
from 
  similars s inner join 
  orders o 
    on o.customer = s.similar 
where 
  -- exclude already bought items
  o.product_id not in ( 
    select product_id 
      from orders 
     where customer = s.customer
  ) 
order by s.customer, o.product_id

Result

customer product_id
A 4
A 5
B 3
B 5
C 2
dani herrera0
Related