SQL- how to sum up the last x days number (days are not consecutive)

Question

I have a table which looks like

ds             Transaction 
2022-01-01      1          
2022-01-02      2          
2022-01-07      1          
2022-01-08      1  

I want to sum up the last 3 days numbers on each date. My original thought is to use window function like

SUM(Transaction) OVER (ORDER BY
ds ASC ROWS BETWEEN 2 PRECEDING AND CURRENT ROW)

It didn't work, because the date is not consecutive. the expected result should be like this:

Date            Transaction 
2022-01-01      1          
2022-01-02      3          
2022-01-07      1          
2022-01-08      2   

thank you for your help!

Answer 1

You can use RANGE instead of ROWS in your window function:

select ds,
SUM(Transact) OVER (ORDER BY
ds ASC RANGE BETWEEN INTERVAL '2' DAY PRECEDING AND CURRENT ROW) as Transaction
from table_name;

Fiddle