By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS';
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;
3 rows affected
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;
3 rows affected
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;
5 rows affected
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
| CUSTOMER_ID | FIRST_NAME | LAST_NAME | DISTINCT_ITEM_CNT |
|---|---|---|---|
| 1 | Abby | Katz | 3 |
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
)
| CUSTOMER_ID | FIRST_NAME | LAST_NAME |
|---|---|---|
| 1 | Abby | Katz |