CODEWITHSUNDEEP
sqlpostgresql
Rasmus Nørskov
Rasmus Nørskov

Reputation: 474

Finding customers that only bought items no one else bought

Below is a list of orders, is there a way to find the person_id of the customers, that has only bought products no one else has bought?

CREATE TABLE orders
AS
  SELECT product_id, person_id
  FROM ( VALUES
    ( 1 , 1 ),
    ( 2 , 1 ),
    ( 2 , 2 ),
    ( 3 , 3 ),
    ( 12, 6 ),
    ( 10, 3 )
  ) AS t(product_id, person_id);

The result would be the following table:

| person_id |
|-----------|
| 3         |
| 6         |

Do i have to find all the people who did buy items no one else bought and create a table that doesn't include those people?

Upvotes: 3

Views: 416

Answers (5)

Renzo
Renzo

Reputation: 27414

Here is another solution:

with unique_products as
  (select product_id
   from orders
   group by product_id
   having count(*) = 1)
select person_id
from orders
  except
select person_id
from orders
where not exists 
       (select * from unique_products where unique_products.product_id = orders.product_id)

First all the identifier of products that appear in a single order are found. Then we subtract from all the persons (in the orders) those which do not have a order with a single product (i.e. all the persons that have at least ordered a product ordered by somebody else).

Upvotes: 0

Clodoaldo Neto
Clodoaldo Neto

Reputation: 125204

The traditional self join with boolean aggregation

select o0.person_id
from
    orders o0
    left join
    orders o1 on o0.product_id = o1.product_id and o0.person_id <> o1.person_id
group by o0.person_id
having bool_and(o1.product_id is null)
;
 person_id 
-----------
         3
         6

Upvotes: 2

dnoeth
dnoeth

Reputation: 60462

This is Gordon's logic using aggregates only:

SELECT person_id
FROM
   (
      SELECT product_id,
              -- if count = 1 it's the only customer who bought this product
             min(person_id) as person_id,
             -- if the combination(person_id,product_id) is unique DISTINCT can be removed
             count(distinct person_id) as cnt
      FROM customers
      GROUP BY product_id
    ) AS dt
GROUP BY person_id
HAVING max(cnt) = 1 -- only unique products

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269463

You want all the products purchased by the person to be unique.

select person_id
from (select t.*,
             min(person_id) over (partition by product_id) as minp,
             max(person_id) over (partition by product_id) as maxp
      from t
     ) t
group by person_id
having sum(case when minp <> maxp then 1 else 0 end) = 0;

You are probably thinking "Huh? What does this do?".

The subquery calculates the minimum person and maximum person on each product. If these are the same, than that one person is the only purchaser.

The having then checks that there are no non-single-purchaser products for a given person.

Perhaps a more intuitive phrasing of the logic would be:

select person_id
from (select t.*,
             count(distinct person_id) over (partition by product_id) as numpersons     
      from t
     ) t
group by person_id
having max(numperson) = 1;

Alas, Postgres doesn't support COUNT(DISTINCT) as a window function.

Upvotes: 2

Teja
Teja

Reputation: 13524

The inline view which is being joined gets all the product_ids which have only one person_id. Once all product_ids are found they will be joined to the original customers table to get the person_ids. This should solve your problem!!

SELECT person_id
  FROM customers c1
INNER JOIN 
       (
          SELECT product_id
            FROM customers
          GROUP BY product_id
          HAVING COUNT(person_id ) = 1
        ) c2
    ON c1.product_id = c2.product_id;

Upvotes: 0

Related Questions