CODEWITHSUNDEEP
sqlsnowflake-cloud-data-platform
Jeremy Ragsdale
Jeremy Ragsdale

Reputation: 25

Find rows where at least 1 transaction occurred each month over the last 3 months

I have a transactions table with a customerID and transactionDate among other fields.

I need a query that would return all customers that have made at least 1 transaction each month over the last 3 months.

I am getting hung up on the “over the last 3 months part”

here is what I have so far but this only gives me people that have made any number of transactions. How do confine it to at least 1 per month?

select * from transactions where transactionDate >= DATEADD(month, -3, GETDATE())

Upvotes: 0

Views: 363

Answers (1)

Lukasz Szozda
Lukasz Szozda

Reputation: 175686

Using:

select CustomerId
from transactions 
where transactionDate >= DATEADD(month, -3, CURRENT_DATE())
group by CustomerId
having COUNT(DISTINCT TO_VARCHAR(transactionDate, 'yyyy-mm')) >= 3;

Upvotes: 0

Related Questions