Postgres 14

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE t (id int, date date, "group" text, amount int);

INSERT INTO t VALUES
( 1    ,to_date('2022-01','yyyy-mm'),'group A',  1100   ),
( 2    ,to_date('2022-01','yyyy-mm'),'group D',  2500   ),
( 3    ,to_date('2022-02','yyyy-mm'),'group A',  3000   ),
( 4    ,to_date('2022-02','yyyy-mm'),'group B',  1000   ),
( 5    ,to_date('2022-02','yyyy-mm'),'group C',  2500   ),
( 6    ,to_date('2022-03','yyyy-mm'),'group A',  2000   ),
( 7    ,to_date('2022-04','yyyy-mm'),'group C',  1000   ),
( 8    ,to_date('2022-05','yyyy-mm'),'group A',  1500   ),
( 9    ,to_date('2022-05','yyyy-mm'),'group D',  2000   ),
( 10   ,to_date('2022-06','yyyy-mm'),'group B',  1000   );

CREATE TABLE



INSERT 0 10


SELECT *, SUM(amount) OVER w AS moving_sum_three_months
FROM t
WINDOW w AS (PARTITION BY "group" ORDER BY "date"
             RANGE BETWEEN INTERVAL '3 months' PRECEDING AND CURRENT ROW)
ORDER BY id


id
date
group
amount
moving_sum_three_months


1
2022-01-01
group A
1100
1100

2
2022-01-01
group D
2500
2500

3
2022-02-01
group A
3000
4100

4
2022-02-01
group B
1000
1000

5
2022-02-01
group C
2500
2500

6
2022-03-01
group A
2000
6100

7
2022-04-01
group C
1000
3500

8
2022-05-01
group A
1500
6500

9
2022-05-01
group D
2000
2000

10
2022-06-01
group B
1000
1000



SELECT 10


SELECT *, SUM(amount) OVER w AS moving_sum_three_months
FROM t
WINDOW w AS (PARTITION BY "group" ORDER BY "date"
             RANGE BETWEEN INTERVAL '2 months' PRECEDING AND CURRENT ROW)
ORDER BY id


id
date
group
amount
moving_sum_three_months


1
2022-01-01
group A
1100
1100

2
2022-01-01
group D
2500
2500

3
2022-02-01
group A
3000
4100

4
2022-02-01
group B
1000
1000

5
2022-02-01
group C
2500
2500

6
2022-03-01
group A
2000
6100

7
2022-04-01
group C
1000
3500

8
2022-05-01
group A
1500
3500

9
2022-05-01
group D
2000
2000

10
2022-06-01
group B
1000
1000



SELECT 10

id	date	group	amount	moving_sum_three_months
1	2022-01-01	group A	1100	1100
2	2022-01-01	group D	2500	2500
3	2022-02-01	group A	3000	4100
4	2022-02-01	group B	1000	1000
5	2022-02-01	group C	2500	2500
6	2022-03-01	group A	2000	6100
7	2022-04-01	group C	1000	3500
8	2022-05-01	group A	1500	6500
9	2022-05-01	group D	2000	2000
10	2022-06-01	group B	1000	1000