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:

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;

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
LEFT JOIN cte ON c.CUSTOMER_ID = cte.CUSTOMER_ID
             AND cte.PURCHASE_DATE < TRUNC(SYSTIMESTAMP) - 30
             AND cte.rn = 1

Check the demo here.

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

CodePudding user response：

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;



select  c.customer_id,
        c.first_name,
        c.last_name,
        max(p.purchase_date) last_purchase
  from  customers c,
        purchases p
  where p.customer_id( ) = c.customer_id
  group by c.customer_id,
           c.first_name,
           c.last_name
  having max(purchase_date) < trunc(systimestamp) - 30
      or max(purchase_date) is null

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    -