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?
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
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