CODEWITHSUNDEEP
sqlsnowflake-cloud-data-platform
Blackdynomite
Blackdynomite

Reputation: 431

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

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

Upvotes: 12

Views: 19341

Answers (2)

Simon D
Simon D

Reputation: 6269

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.

Upvotes: 17

Lukasz Szozda
Lukasz Szozda

Reputation: 175716

The approach with INTERVAL was almost correct.

SELECT * 
FROM data d 
WHERE d.DATE_CREATED <= current_date + interval '30 days'  
                        -- this date is from the future instead of the past

=>

SELECT * 
FROM data d 
WHERE d.DATE_CREATED >= current_date - interval '30 days'

Upvotes: 8

Related Questions