Skip to content
Advertisement

Snowflake SQL Filter by transactions in the last rolling 30 days

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

Desired output:

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.

Advertisement

Answer

To get the customers that have made at least one purchase in the last 30 days you can do this:

the dateadd function shown above returns a date 30 days prior to the current date (when the SQL was run)

User contributions licensed under: CC BY-SA
9 People found this is helpful
Advertisement