I have a query that pulls transaction data, by different account. I want to only pull in the first 12 months of the trailing transaction by sale. For Example - the two records wouldnt pull because they fall out of the twelve month window since the first transaction date. how would i write a query like this? any help if very much appreciated!
Account Date
Apples 1/1/2022
Apples 5/1/2022
Apples 8/1/2022
Apples 9/1/2022
Apples 12/1/2022
--Apples 1/1/2023
--Apples 7/1/2023
Bananas 5/1/2022
Bananas 6/1/2022
Bananas 7/1/2022
Bananas 12/1/2022
Oranges 1/1/2023
Oranges 2/1/2023
Oranges 3/1/2023
Oranges 4/1/2023
You can do this simply and efficiently with window functions:
select account, date
from (
select t.*, min(date) over(partition by account) as min_date
from mytable t
) t
where date < min_date + interval '1' year
The idea is to compute the earliest date of each account with a window min(). We can then use this information to filter the dataset.
You did not tag which database you are running. The syntax for date arithmetics does vary with database engines: the above is standard ANSI SQL, which you might need to adapt to your RDBMS.