with t as(
select substr(SALES_DATE,1,7) as w, sum(case when updated_no is null then sales_amt else 0 end)as s,
rank() over(order by substr(SALES_DATE,1,7)) as r
from sales
where SALES_DATE>='2022-01-01' and SALES_DATE<='2023-12-31'
group by 1
order by 1
),
t1 as (
select t.w as 'YEAR_MONTH',
t.s as 'MONTH_AMT',
sum(t0.s) as 'CUML_AMT'
from t
inner join t as t0 on
13<=t0.r and t0.r <= t.r
where 13<=t.r and t.r<=24
group by 1,2
order by 1
),
t2 as (
select t.w as 'YEAR_MONTH',
t.s as 'MONTH_AMT',
sum(t0.s) as 'YEAR_MOVE_AMT'
from t
inner join t as t0 on
t.r-11<=t0.r and t0.r <= t.r
where 13<=t.r and t.r<=24
group by 1,2
order by 1
)
select t1.*, t2.YEAR_MOVE_AMT
from t1
inner join t2 on t1.YEAR_MONTH=t2.YEAR_MONTH
order by 1 asc