Oracle 21c

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE customers 
(CUSTOMER_ID, FIRST_NAME, LAST_NAME) AS
SELECT 1, 'Faith', 'Mazzarone' FROM DUAL UNION ALL
SELECT 2, 'Lisa', 'Saladino' FROM DUAL UNION ALL
SELECT 3, 'Jerry', 'Torchiano' FROM DUAL;

3 rows affected

CREATE TABLE items 
(PRODUCT_ID, PRODUCT_NAME, PRICE) AS
SELECT 100, 'Black Shoes', 79.99 FROM DUAL UNION ALL
SELECT 101, 'Brown Pants', 111.99 FROM DUAL UNION ALL
SELECT 102, 'White Shirt', 10.99 FROM DUAL;
3 rows affected

CREATE TABLE purchases
(CUSTOMER_ID, PRODUCT_ID, QUANTITY, PURCHASE_DATE) AS
SELECT 1, 101, 3, TIMESTAMP'2022-10-11 09:54:48' FROM DUAL UNION ALL
SELECT 1, 100, 1, TIMESTAMP '2022-10-12 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, 3, TIMESTAMP '2022-10-17 19:34:58' FROM DUAL UNION ALL
SELECT 3, 101,1, TIMESTAMP '2022-12-11 09:54:48' FROM DUAL UNION ALL
SELECT 3, 102,1, TIMESTAMP '2022-12-17 19:04:18' FROM DUAL UNION ALL
SELECT 3, 102, 4,TIMESTAMP '2022-12-12 21:44:35' + NUMTODSINTERVAL ( LEVEL * 2, 'DAY') FROM    dual
CONNECT BY  LEVEL <= 5;
11 rows affected

SELECT c.*
FROM customers c
WHERE NOT EXISTS (SELECT 1
                  FROM purchases p 
                   WHERE c.customer_id  = p.customer_id AND                                                p.PURCHASE_DATE >= TRUNC(SYSTIMESTAMP) - NUMTODSINTERVAL (30, 'DAY')
 AND
p.PURCHASE_DATE < TRUNC(SYSTIMESTAMP)  
                 );


CUSTOMER_ID
FIRST_NAME
LAST_NAME


2
Lisa
Saladino

1
Faith
Mazzarone


WITH cte AS (
    SELECT CUSTOMER_ID,
           PURCHASE_DATE,
           ROW_NUMBER() OVER(PARTITION BY CUSTOMER_ID ORDER BY PURCHASE_DATE DESC) AS rn
    FROM purchases
)
SELECT c.*, cte.PURCHASE_DATE
FROM       customers c
INNER JOIN cte ON c.CUSTOMER_ID = cte.CUSTOMER_ID
              AND cte.PURCHASE_DATE < TRUNC(SYSTIMESTAMP) - 30
              AND cte.rn = 1


CUSTOMER_ID
FIRST_NAME
LAST_NAME
PURCHASE_DATE


1
Faith
Mazzarone
12-OCT-22 19.04.18.000000000

2
Lisa
Saladino
17-OCT-22 19.34.58.000000000

CUSTOMER_ID	FIRST_NAME	LAST_NAME	PURCHASE_DATE
1	Faith	Mazzarone	12-OCT-22 19.04.18.000000000
2	Lisa	Saladino	17-OCT-22 19.34.58.000000000