I’m trying to figure out how to get a sum of value by transaction dates over a 90 day period and repeated over a single year’s worth of transaction dates.
This is what I have
transaction_date | value |
---|---|
01-01-2024 | 20 |
02-01-2024 | 50 |
03-01-2024 | 30 |
With the intended outcome of:
transaction_date | date_diff | value |
---|---|---|
01-01-2024 | 03-10-2023 | (20+ all transaction date value between 03-10-2023 and 01-01-2023 |
02-01-2024 | 04-10-2023 | 20 + 50 + predated value |
03-01-2024 | 05-10-2023 | 20 + 50 + 30 + predated value |
For date_diff, I got the date via:
(transaction_date) – 90 DAYs as date_diff90
2
Answers
Try to using a self join and Interval function
I did n’t test yet but it should be something like that
Using Interval 90 day to calculate previous 90 days
A simple correlated subquery will do.
Here is the query: