Select Customer ID who hasnt purchased product X

Question

I have a table of customer IDs and Products Purchased. A customer ID can purchase multiple products over time.

customerID, productID

In BigQuery I need to find the CustomerID for those who have not purchased product A.

I've been going around in circles trying to do self joins, inner joins, but I'm clueless.

Any help appreciated.

Answer 1

select customerID
from your_table
group by customerID
having sum(case when productID = 'A' then 1 else 0 end) = 0

and to check if it only contains a name

sum(case when productID contains 'XYZ' then 1 else 0 end) = 0

Answer 2

If you have a customer table, you might want:

select c.*
from customers c
where not exists (select 1 from t where t.customer_id = c.customer_id and t.proectID = 'A');

This will return customers who have made no purchases as well as those who have purchased all but product A. Of course, the definition of a customer in your data might be that the customer has made a purchase, in which case I like Juergen's solution.

Answer 3

Below is for BigQuery Standard SQL

#standardSQL
SELECT CustomerID
FROM `project.dataset.yourTable`
GROUP BY CustomerID
HAVING COUNTIF(Product = 'A') = 0

You can test / play with it using dummy data as below

#standardSQL
WITH `project.dataset.yourTable` AS (
  SELECT 1234 CustomerID, 'A' Product UNION ALL
  SELECT 11234, 'A' UNION ALL
  SELECT 4567, 'A' UNION ALL
  SELECT 7896, 'C' UNION ALL
  SELECT 5432, 'B' 
)
SELECT CustomerID
FROM `project.dataset.yourTable`
GROUP BY CustomerID
HAVING COUNTIF(Product = 'A') = 0  

how would I adjust this so it could be productID contains "xyz"

#standardSQL
WITH `project.dataset.yourTable` AS (
  SELECT 1234 CustomerID, 'Axyz' Product UNION ALL
  SELECT 11234, 'A' UNION ALL
  SELECT 4567, 'A' UNION ALL
  SELECT 7896, 'Cxyz' UNION ALL
  SELECT 5432, 'B' 
)
SELECT CustomerID
FROM `project.dataset.yourTable`
GROUP BY CustomerID
HAVING COUNTIF(REGEXP_CONTAINS(Product, 'xyz')) = 0