SQL look back

I have data with customer  information i.e. on which data they have purchased anything. Now I want to take those customers who have not purchased anything in the last 60 days. How can I do that in SQL?

2replies Oldest first
  • Oldest first
  • Newest first
  • Active threads
  • Popular
  • Hi pawan, one possible approach... Select from your customer list and outer join a sub-query containing purchases occurring during the trailing 60 days joined to your customer list on customer_id.

    Say you alias your outer joined table as 'purchases'.  If you GROUP BY customer_id you can use a CASE statement or logic in your WHERE clause to retrieve just the members of your customer list who have no data in the outer joined table of purchases.  

    Reply Like
  • another way is to do a left join from customers to purchases with the time constraint in the ON clause and then in the WHERE only accept the failed match lines:
     

    create table customers (id int);
    create table purchases (customer_id int,time timestamp);
    insert into customers values (1),(2),(3);
    insert into purchases values (1,'2018-05-28'),(2,'2018-01-13');
    
    select c.id
    from customers c
    left join purchases p on c.id = p.customer_id and p.time >= '2018-4-01'
    where p.customer_id is null;
    
    Reply Like
reply to topic
Like Follow
  • Status Answered
  • 3 mths agoLast active
  • 2Replies
  • 273Views
  • 3 Following

Create A New Discussion

Share your thoughts