Query to calculate “trailing twelve-month sum(column)” for 100s of dates in one query?

I have this table:

Dividends History Table

id | date | dividends_received_value
 1 | 2020-01-02 | 0.00
 2 | 2020-01-03 | 1.30
 3 | 2020-01-04 | 0.45
...

I’m trying to figure out the most efficient way to get:

• One row for each date
• Each row contains the

sum(dividends_received_value) for the past year of data relative to the date in that row (“trailing twelve months”)

Can this be done purely in SQL?

Is there any way to perform this calculation within a reasonable amount of time, if I have like 3000 dates/rows?

Answer

You can use a window function:

select t.*,
       sum(dividends_received_value) over (order by date
                                           range between interval 1 year preceding and current row
                                          ) as trailing_12_month
from t;

If you can have multiple rows per date, then put this logic in an aggregation query:

select t.date, sum(dividends_received_value) as on_day,
       sum(sum(dividends_received_value)) over (order by date
                                                range between interval 1 year preceding and current row
                                               ) as trailing_12_month
from t
group by t.date;

Here is a db<>fiddle.