MySQL 8.0

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

select version();


version()


8.0.26


create table tblOrder (
  week decimal,
  product varchar(5),
  sale decimal
  );
  
 insert into tblOrder Values(1,'ABC', 2);
 insert into tblOrder Values(1,'ABC', 1);
 insert into tblOrder Values(2,'ABC', 1);
 insert into tblOrder Values(3,'ABC', 5);
 insert into tblOrder Values(4,'ABC', 1);
 
 insert into tblOrder Values(2,'DEF', 5);
 insert into tblOrder Values(3,'DEF', 1);
 insert into tblOrder Values(3,'DEF', 2);
select a.*,
  sum(sale) over(partition by product 
                 order by week range between 1 preceding and 1 preceding
                ) as ProductPreviousWeekSales
from tblOrder a
order by product, week;


week
product
sale
ProductPreviousWeekSales


1
ABC
2
null

1
ABC
1
null

2
ABC
1
3

3
ABC
5
1

4
ABC
1
5

2
DEF
5
null

3
DEF
1
5

3
DEF
2
5

week	product	sale	ProductPreviousWeekSales
1	ABC	2	null
1	ABC	1	null
2	ABC	1	3
3	ABC	5	1
4	ABC	1	5
2	DEF	5	null
3	DEF	1	5
3	DEF	2	5