How to make a query showing purchases of a client on the same day, but only if those were made in diffrent stores (oracle)?

Question

I want to show cases of clients with at least 2 purchases on the same day. But I only want to count those purchases that were made in different stores. So far I have:

Select Purchase.PurClientId, Purchase.PurDate, Purchase.PurId
from Purchase  
join 
( 
 Select count(Purchase.PurId), 
   Purchase.PurClientId, 
   to_date(Purchase.PurDate)
 from Purchases
 group by Purchase.PurClientId, 
      to_date(Purchase.PurDate)
 having count (Purchase.PurId) >=2 
 ) k 
    on k.PurClientId=Purchase.PurClientId

But I have no clue how to make it count purchases only if those were made in different stores. The column which would allow to identify shop is Purchase.PurShopId. Thanks for help!

Answer 1

You can use:

SELECT PurId,
       PurDate,
       PurClientId,
       PurShopId
FROM   (
  SELECT p.*,
         COUNT(DISTINCT PurShopId) OVER (
           PARTITION BY PurClientId, TRUNC(PurDate)
         ) AS num_stores
  FROM   Purchase p
)
WHERE  num_stores >= 2;

Or

SELECT *
FROM   Purchase p
WHERE  EXISTS(
  SELECT 1
  FROM   Purchase x
  WHERE  p.purclientid = x.purclientid
  AND    p.purshopid != x.purshopid
  AND    TRUNC(p.purdate) = TRUNC(x.purdate)
);

Which, for the sample data:

CREATE TABLE purchase (
  purid PRIMARY KEY,
  purdate,
  purclientid,
  PurShopId
) AS
SELECT 1, DATE '2021-01-01', 1, 1 FROM DUAL UNION ALL
SELECT 2, DATE '2021-01-02', 1, 1 FROM DUAL UNION ALL
SELECT 3, DATE '2021-01-02', 1, 2 FROM DUAL UNION ALL
SELECT 4, DATE '2021-01-03', 1, 1 FROM DUAL UNION ALL
SELECT 5, DATE '2021-01-03', 1, 1 FROM DUAL UNION ALL
SELECT 6, DATE '2021-01-04', 1, 2 FROM DUAL;

Both output:

PURID	PURDATE	PURCLIENTID	PURSHOPID
2	2021-01-02 00:00:00	1	1
3	2021-01-02 00:00:00	1	2

db<>fiddle here