My goal is to select the count of distinct customer_id's who have not made a purchase in the rolling 30 day period.
I am getting an error, which I can't figure out.
"invalid datatype".
I have a test CASE setup below. Any help would be greatly appreciated. If there is a better or more efficient way to achieve this task I am open to ALL suggestions. Thanks to all who answer.
ALTER SESSION SET NLS_TIMESTAMP_FORMAT = 'DD-MON-YYYY HH24:MI:SS.FF';
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;
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 lapsed_info AS (SELECT customer_id,
purchase_date,
CASE WHEN TRUNC(SYSTIMESTAMP) - purchase_date <= INTERVAL '30' DAY THEN NULL
WHEN COUNT(*) OVER (PARTITION BY customer_id ORDER BY purchase_date RANGE BETWEEN 1 FOLLOWING AND 30 FOLLOWING) = 0 THEN purchase_date + INTERVAL '30' DAY
ELSE NULL
END lapsed_date
FROM purchases),
dates AS (SELECT TO_TIMESTAMP('12/01/2022', 'MM/DD/YYYY') + LEVEL -1 dt
FROM dual
CONNECT BY TO_TIMESTAMP(12/01/2022', 'MM/DD/YYYY') + LEVEL -1 <= TRUNC(SYSTIMESTAMP))
SELECT dates.dt,
COUNT(li.lapsed_date) lapsed_count
FROM dates
LEFT OUTER JOIN lapsed_info li ON dates.dt = li.lapsed_date
GROUP BY dates.dt
ORDER BY dates.dt;