I have table in Teradata SQL about transactions like below:
ID | NAME | DATE
------------
123 | Adam | 10-09-2021
123 | Adam | 11-09-2021
333 | Jane | 15-06-2021
456 | Tom | 11-02-2016
123 | Adam | 08-09-2021
333 | Jane | 22-01-2021
123 | Adam | 23-05-2017
- ID - id of client
- NAME - name of client
- DATE - date of transaction
And I would like to select only these clients who made minimum 2 transactions during last 30 days from today date (13-09-2021).
So as a result I need something like below, because only client Adam made MINIMUM 2 transactions during last 40 days from current date.
ID | NAME
------------
123 | Adam
How can I do that in Teradata SQL ?
CodePudding user response:
Hmmm . . . if you only want the clients, then this is filtering and aggregation:
select id, name
from transactions
where date >= current_date - 30
group by id, name
having count(*) >= 2;