I'm trying to find customers that bought the same item more than once on different days.
The professor wants 3 solutions to the problem but I have 1.5. For the second solution I can't seem to get all the information that I have in the first solution and was wondering if someone can point me in the right direction.
Lastly, could my third solution be written with a self JOIN or perhaps lead() function. Thanks for your time, expertise and patience.
CREATE TABLE customers
(CUSTOMER_ID, FIRST_NAME, LAST_NAME) AS
SELECT 1, 'Abby', 'Katz' FROM DUAL UNION ALL
SELECT 2, 'Lisa', 'Saladino' FROM DUAL UNION ALL
SELECT 3, 'Jerry', 'Torchiano' 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'2022-10-11 09:54:48' FROM DUAL UNION ALL
SELECT 1, 100, 1, TIMESTAMP '2022-10-11 19:04:18' FROM DUAL UNION ALL
SELECT 2, 101,1, TIMESTAMP '2022-10-11 09:54:48' FROM DUAL UNION ALL
SELECT 2,101,1, TIMESTAMP '2022-10-17 19:04:18' FROM DUAL UNION ALL
SELECT 3, 101,1, TIMESTAMP '2022-10-11 09:54:48' FROM DUAL UNION ALL
SELECT 3,102,1, TIMESTAMP '2022-10-17 19:04:18' FROM DUAL;
WITH temp AS
( SELECT c.first_name,
c.last_name,
i.product_name,
TRUNC (p.purchase_date),
COUNT (*) OVER (PARTITION BY c.first_name, c.last_name, i.product_name) num_purchases
FROM purchases p
JOIN customers c ON c.customer_id = p.customer_id
JOIN items i ON i.product_id = p.product_id
GROUP BY c.first_name, c.last_name, i.product_name, TRUNC (p.purchase_date))
SELECT DISTINCT first_name, last_name, product_name, num_purchases
FROM temp
WHERE num_purchases > 1;
/* half implemented */
SELECT DISTINCT c.first_name, c.last_name
FROM customers c
INNER JOIN purchases p
ON p.customer_id = c.customer_id
WHERE EXISTS (
SELECT 1
FROM purchases p2
WHERE p2.customer_id = p.customer_id AND
p2.product_id = p.product_id AND
TRUNC(p2.purchase_date) <> TRUNC(p.purchase_date)
);
Want this output for all attempts
FIRST_NAME LAST_NAME PRODUCT_NAME NUM_PURCHASES
Lisa Saladino Brown Shoes 2