I have a data table something similar to having a customer ID and an item purchase date as shown below. As a filter, I want to return customer ID IFF a given Customer ID has at least 1 purchase in the last 30 rolling days.
Is this something that can be done with a simple WHERE
clause ? For my purposes, this data table has many records where a customer ID might have hundreds of transactions
Customer ID Item Date Purchased
233 2021-05-27
111 2021-05-27
111 2021-05-21
23 2021-05-12
412 2021-03-11
111 2021-03-03
Desired output:
Customer ID
233
111
23
Originally thought to use a CTE to initially filter out any users that don’t have at least 1 item purchase within the last 30 days. Tried the following two different where statements but both didn’t work returning incorrect date timeframes.
SELECT *
FROM data d
WHERE 30 <= datediff(days, d.ITEM_PURCHASE_DATE, current_date) X
WHERE t.DATE_CREATED <= current_date + interval '30 days' X
Advertisement
Answer
To get the customers that have made at least one purchase in the last 30 days you can do this:
select distinct
customer_id
from sample_table
where item_date_purchased > dateadd(day, -30, current_date());
the dateadd function shown above returns a date 30 days prior to the current date (when the SQL was run)