Display customers who haven't made a purchase in less than 30 days

Question

I'm trying to list all the customers who haven't had a purchase in the past 30 days with the following query:

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

which results in the following output:

CUSTOMER_ID	FIRST_NAME	LAST_NAME
1	Faith	Mazzarone
2	Lisa	Saladino

This query appears to be working.

But I also want to display the last "purchase_date" value 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?

Here's the DDL to reproduce my environment:

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;

Answer 1

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