Customers that purchased every item

June 10, 2024

I’m trying to construct a query that will show me a list of customers that purchased all items.

I’m thinking perhaps the distinct and EXISTS option or perhaps a count() may be a solution but I’m unsure how to put this all together. Any help would be greatly appreciated.

Below is my sample data and expected output.


CREATE TABLE customers (CUSTOMER_ID, FIRST_NAME, LAST_NAME) AS 
SELECT 1, 'Abby', 'Katz' FROM DUAL UNION ALL
SELECT 2, 'Lisa', 'Jones' FROM DUAL UNION ALL 
SELECT 3, 'Joanne','Dalton' FROM DUAL; 

CREATE TABLE items (PRODUCT_ID, PRODUCT_NAME) AS 
SELECT 100, 'Black Shoes' FROM DUAL UNION ALL
 SELECT 101, 'Brown Shoes' FROM DUAL UNION ALL
SELECT 102, 'White Shoes' FROM DUAL; 

CREATE TABLE purchases (CUSTOMER_ID, PRODUCT_ID, QUANTITY, PURCHASE_DATE) AS
SELECT 1, 100, 1, TIMESTAMP'2024-05-11 09:54:48' FROM DUAL UNION ALL
SELECT 1, 101, 1, TIMESTAMP'2024-05-11 19:54:48' FROM DUAL UNION ALL
SELECT 1, 102, 1, TIMESTAMP'2024-06-09 14:54:48' FROM DUAL UNION ALL
SELECT 3, 100, 1, TIMESTAMP'2024-06-09 11:34:44' FROM DUAL UNION ALL
SELECT 3,102,1, TIMESTAMP '2024-05-10 19:04:18' FROM DUAL;

CUSTOMER_ID FIRST_NAME LAST_NAME DISTINCT_ITEM_CNT

1 Abby Katz 3

>Solution :

You can use a PARTITIONed OUTER JOIN to generate the count:

SELECT c.*,
       p.distinct_item_cnt
FROM   customers c
       INNER JOIN (
         SELECT customer_id,
                COUNT(DISTINCT p.product_id) AS distinct_item_cnt
         FROM   items i
                LEFT OUTER JOIN purchases p
                PARTITION BY (p.customer_id)
                ON (p.product_id  = i.product_id)
         GROUP BY p.customer_id
         HAVING COUNT(DISTINCT p.product_id) = COUNT(i.product_id)
       ) p
       ON p.customer_id = c.customer_id

Which, for the sample data, outputs:

CUSTOMER_ID	FIRST_NAME	LAST_NAME	DISTINCT_ITEM_CNT
1	Abby	Katz	3

If you do not want the final DISTINCT_ITEM_CNT column then you can find the customers using NOT EXISTS:

SELECT *
FROM   customers c
WHERE  NOT EXISTS(
         SELECT 1
         FROM   items i
                LEFT OUTER JOIN purchases p
                ON (   p.product_id  = i.product_id
                   AND p.customer_id = c.customer_id)
         WHERE  p.customer_id IS NULL
       )

fiddle