Display customers who haven't had a purchase in 30 days-CodePudding

I'm trying to list all the customers who haven't had a purchase in the past 30 days.

In my test CASE below that appears to be working. I also want to display the last purchase_date along with the customer information or NULL if the customer has never made a purchase. I can't seem to figure out how to do that. Can someone please help me out.


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, '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;

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 LAST_PURCHASE
1   Faith   Mazzarone ??????
2   Lisa    Saladino  ??????

CodePudding user response：

You can use the ROW_NUMBER window function to detect the last purchase date of each customer, then join back to the customers infos to get full names for each customer.

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

Check the demo here.

Note: Use LEFT JOIN only if you want customers that have never bought.

CodePudding user response：

You can display the last purchase date along with the customer information by using the MAX() function to get the most recent purchase date for each customer and then joining that information with the customers table.

SELECT c.*, p.last_purchase
FROM customers c
LEFT JOIN (SELECT customer_id, MAX(purchase_date) as last_purchase
           FROM purchases
           GROUP BY customer_id) p
ON c.customer_id = p.customer_id
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)  
                 );