Find purchase if same item on different days

Question

I'm trying to find customers that bought the same item more than once in different days. I got it partially working. I can't get the customer first/last name and item_name without adding it to the group by clause. In addition, I want to include a count if how many times the same uten was purchased on different days.

I suspect that group by is probably not the best solution. Would this be better solved using a self JOIN or perhaps a lead?

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 CTE as (
 SELECT customer_id
                ,product_id 
                ,trunc(purchase_date)
  FROM purchases
  GROUP BY customer_id 
                    ,product_id
                   ,trunc(purchase_date)
)
SELECT  customer_id, product_id
FROM CTE
GROUP BY customer_id                  ,product_id 
HAVING COUNT(1)>1

Please either ask about 1 bad query/function with the obligatory [mre] & why you think it should return something else at the 1st subexpression that it doesn't give what you expect, justified by reference to authoritative documentation, or ask about your overall goal giving working parts you can do & ideally a [mre]. But please ask about the former 1st because misconceptions in the former will get in the way of understanding the latter. And bad code doesn't tell us what you wish it would do. [ask] [Help] — philipxy, Oct 28 '22 at 23:12

Tim Biegeleisen · Answer 1 · 2022-10-28T12:14:02.957

I would use exists logic here:

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)
);

In plain English, the above query says to find all customers who bought the same product but on different dates.

score 2 · Answer 2 · answered Oct 28 '22 at 12:19

2

This might be one option: use count function in its analytic form and the fetch rows where that count is larger than 1; according to data you posted, it is Lisa who bought brown shoes on two different dates.

SQL> WITH
  2     temp
  3     AS
  4        (  SELECT c.first_name,
  5                  i.product_name,
  6                  TRUNC (p.purchase_date),
  7                  COUNT (*) OVER (PARTITION BY c.first_name, i.product_name) cnt
  8             FROM purchases p
  9                  JOIN customers c ON c.customer_id = p.customer_id
 10                  JOIN items i ON i.product_id = p.product_id
 11         GROUP BY c.first_name, i.product_name, TRUNC (p.purchase_date))
 12  SELECT DISTINCT first_name, product_name, cnt
 13    FROM temp
 14   WHERE cnt > 1;

FIRST PRODUCT_NAM        CNT
----- ----------- ----------
Lisa  Brown Shoes          2

SQL>

answered Oct 28 '22 at 12:19

Littlefoot

131,892
15
35
57

Littlefoot thanks that's exactly what I was looking for. Could this have been done with a self JOIN. Just curious – Beefstu Oct 28 '22 at 12:37
You're welcome. Which self join do you have on mind? There are 3 tables involved. – Littlefoot Oct 28 '22 at 12:38
Littlefoot on the purchases table but I see Tim posted something similar that satisfied my curiosity – Beefstu Oct 28 '22 at 12:54
OK, but I don't see how that query returns values you wanted. To remind you: "I can't get the customer first/last name and **item_name** without adding it to the group by clause. In addition, I want to include a **count** if how many times the same uten was purchased on different days." – Littlefoot Oct 28 '22 at 18:44

Find purchase if same item on different days

2 Answers2