Find the customer who did not purchase form past 90 days

We are working on the e-commerce project. We get a requirement that we need to get the list of the customer those who are not purchase any thing form last 90 days. Business way to send some offer notification to then to purchase this from our portal.

Let’s see

For the demo we are creating tables and inserting few records in this table.

CREATE TABLE Customers (     CustomerID   INT,     CustomerName VARCHAR(50),               Mobile_No  varchar(10),               Email varchar(50) ); CREATE TABLE Orders (     OrderID     INT,     CustomerID  INT,     OrderDate   DATE,     Amount      DECIMAL(10,2) ); --Inserting records INSERT INTO Customers VALUES (1, 'Amit' ,'999999XXXX','Amit@gmail.com'), (2, 'Ganesh','999999XXX1','Ganesh@gmail.com'), (3, 'Bagesh','999999XXX2','Bagesh@gmail.com'), (4, 'Ramesh','999999XXX3','Ramesh@gmail.com');  INSERT INTO Orders VALUES (101, 1, DATEADD(DAY, -30, GETDATE()), 5000),   -- Recent (102, 1, DATEADD(DAY, -10, GETDATE()), 3000), (103, 2, DATEADD(DAY, -120, GETDATE()), 7000),  -- Old (104, 3, DATEADD(DAY, -200, GETDATE()), 4000);  -- Old -- Ramesh (ID 4) never purchased

See the records into the table.

There is multiple way to get the list of the customer.

Using NOT EXISTS

Below query is used to get the list of the customer.

SELECT c.customerid,        c.customername,        c.mobile_no,        c.email FROM   customers c WHERE  NOT EXISTS (SELECT 1                    FROM   orders o                    WHERE  o.customerid = c.customerid                           AND o.orderdate >= Dateadd(day, -90, Getdate()));

 

See there Ganesh and Bagesh had purchase 90 days before but Ramesh had not yet purchased anything but still getting his name. business will send then mobile notification or Email notification.

Other way to get the list using group by and having clause

 See below

SELECT c.customerid,        c.customername,                  c.mobile_no,        c.email FROM   customers c        LEFT JOIN orders o               ON c.customerid = o.customerid GROUP  BY c.customerid,           c.customername,                                c.mobile_no,           c.email HAVING Max(o.orderdate) < Dateadd(day, -90, Getdate())         OR Max(o.orderdate) IS NULL;

 See the result.

We will prefer the first approach.