All apologies for my previous post about rolling periods, please ignore or DELETE it!! I was relayed the wrong information previously. I've started a new question and I hope that is the correct protocol.
The requirement is I need to find all customers who have no purchases in 30 days or none at all.
Below is my test CASE and data. I believe I am almost there but I am missing a row in my output. I'm thinking I need a left JOIN somewhere or something similar?
ALTER SESSION SET NLS_TIMESTAMP_FORMAT = 'DD-MON-YYYY HH24:MI:SS.FF';
ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YYYY HH24:MI:SS';
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, 'Micheal', 'Palmice' FROM DUAL UNION ALL
SELECT 4, 'Jerry', 'Torchiano' FROM DUAL;
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;
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;
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 LAST_PURCHASE
1 Faith Mazzarone 12-OCT-2022 19:04:18.000000
2 Lisa Saladino 17-OCT-2022 19:34:58.000000
Expected output
==============
CUSTOMER_ID FIRST_NAME LAST_NAME LAST_PURCHASE
1 Faith Mazzarone 12-OCT-2022 19:04:18.000000
2 Lisa Saladino 17-OCT-2022 19:34:58.000000
4 Jerry Torchiano -