Postgres 12

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

create table data (Item text, Week int, sales int);

CREATE TABLE


insert into data 
values
('a',  1, 10),
('a',  2, 10),
('a',  4, 10),
('a',  7, 10),
('a',  8, 10),
('a', 10, 10),
('b',  1, 10),
('b',  2, 10),
('b',  4, 10),
('b',  7, 10),
('b',  8, 10),
('b', 10, 10);

INSERT 0 12


select item, 
       week,
       sum (sales) over (partition by item order by week range between 3 preceding and current row)
from data
order by item, week;


item
week
sum


a
1
10

a
2
20

a
4
30

a
7
20

a
8
20

a
10
30

b
1
10

b
2
20

b
4
30

b
7
20

b
8
20

b
10
30



SELECT 12


with items as (
  select distinct item
  from data
), all_weeks as (
  select i.item, g.week
  from items i
    cross join ( values (1),(2),(3),(4),(5),(6),(7),(8),(9),(10) ) as g(week) 
)    
select t.item,
       t.week,
       sum (d.sales) over (partition by t.item order by t.week range between 3 preceding and current row)
from all_weeks t
  left join data d on d.item = t.item and d.week = t.week;


item
week
sum


a
1
10

a
2
20

a
3
20

a
4
30

a
5
20

a
6
10

a
7
20

a
8
20

a
9
20

a
10
30

b
1
10

b
2
20

b
3
20

b
4
30

b
5
20

b
6
10

b
7
20

b
8
20

b
9
20

b
10
30



SELECT 20

item	week	sum
a	1	10
a	2	20
a	3	20
a	4	30
a	5	20
a	6	10
a	7	20
a	8	20
a	9	20
a	10	30
b	1	10
b	2	20
b	3	20
b	4	30
b	5	20
b	6	10
b	7	20
b	8	20
b	9	20
b	10	30

item	week	sum
a	1	10
a	2	20
a	3	20
a	4	30
a	5	20
a	6	10
a	7	20
a	8	20
a	9	20
a	10	30
b	1	10
b	2	20
b	3	20
b	4	30
b	5	20
b	6	10
b	7	20
b	8	20
b	9	20
b	10	30

item	week	sum
a	1	10
a	2	20
a	3	20
a	4	30
a	5	20
a	6	10
a	7	20
a	8	20
a	9	20
a	10	30
b	1	10
b	2	20
b	3	20
b	4	30
b	5	20
b	6	10
b	7	20
b	8	20
b	9	20
b	10	30