with SALES_YM as(
    select
        strftime('%Y-%m', SALES_DATE) as YM
        ,sum(SALES_AMT) as MON_AMT
    from SALES
    where
        YM >= '2022-01' --selectで定義したYMをwhereで使っていいの？
        and
        UPDATED_NO is null
    group by YM
)
, Z_CHART as(
    select
        YM as YEAR_MONTH
        , MON_AMT as MONTH_AMT
        , sum(MON_AMT) over(
            order by YM rows between 11 preceding and current row
        ) as YEAR_MOVE_AMT
    from SALES_YM
)
select
    YEAR_MONTH
    , MONTH_AMT
    , SUM(MONTH_AMT) over(order by YEAR_MONTH) as CUML_AMT
    , YEAR_MOVE_AMT
from Z_CHART
where YEAR_MONTH BETWEEN '2023-01' and '2023-12'
order by YEAR_MONTH asc
;



--select
    --2023-01から2023-12
--    strftime('%Y-%m', SALES_DATE) as YEAR_MONTH,
    --月次売上金額
--    sum(SALES_AMT) as MONTH_AMT,
    --売上累計金額 
--    sum(sum(SALES_AMT))
--        over(order by 
--            strftime('%Y-%m', SALES_DATE)
--            )
--    as CUML_AMT
    --移動年計金額 as YEAR_MOVE_AMT
--from SALES
--where
--    UPDATED_NO IS NULL
--    and
--    SALES_DATE LIKE '2023-%'
--group by strftime('%Y-%m', SALES_DATE)
--order by strftime('%Y-%m', SALES_DATE) asc
--;