How to get total amount per previous weeks

Question

I have this table for example:

Date	amount
2021-02-16T21:06:38	10
2021-02-16T21:07:01	5
2021-02-17T01:10:12	-1
2021-02-19T12:00:00	3
2021-02-24T12:00:00	20
2021-02-25T12:00:00	-1

I want the total amount of all previous weeks, per week. So the result in this case would be:

Date	amount
2021-02-15	0
2021-02-22	17
2021-03-01	36

Note: The dates are now the start of each week (Monday). Any help would this would be greatly appreciated.

Answer 1

Try This:

select week_date, sum(amount) over (order by week_date ) 
from (
SELECT date(date_) + cast(abs(extract(dow FROM date_) -7 ) + 1 as int) "week_date",
sum(amount) "amount"
from example group by 1) t 

DEMO

Above Query will cover only the week in which transaction records are there. If you want to cover all missing week then try below query:

with cte as (
SELECT date(date_) + cast(abs(extract(dow FROM date_) -7 ) + 1 as int) "week_date",
sum(amount) "amount"
from example group by 1
)
select 
t1."Date",coalesce(sum(cte.amount) over (order by t1."Date"),0)
from cte right join 
(select generate_series(min(week_date)- interval '1 week', max(week_date),interval '1 week') "Date" from cte) t1 on cte.week_date=t1."Date"

DEMO