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

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

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

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)

);

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