--月次売上 = 売上金額合計(SALES_AMT)を月別に集計
WITH tmp AS(
    SELECT
        STRFTIME("%Y-%m",SALES_DATE) AS year_month
        ,SUM(SALES_AMT) AS sum_sales_atm
    FROM SALES
    GROUP BY 1
)

--売上累計 = 2023年1月から該当月までの月次売上を合計
,tmp2 AS(
    SELECT 
        year_month
        ,SUM(sum_sales_atm) OVER (PARTITION BY STRFTIME("%Y",year_month) ORDER BY year_month
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS CUML_AMT
    FROM tmp
    WHERE year_month BETWEEN "2023-01" AND "2023-12"
    GROUP BY 1
)

--集計した月次売上金額と集計した売上累計金額のマージ
,tmp3 AS(
    SELECT
        tmp.year_month AS  YEAR_MONTH
        ,tmp.sum_sales_atm AS MONTH_AMT
        ,tmp2.cuml_amt AS  CUML_AMT
    FROM tmp2
    LEFT JOIN tmp
    ON tmp2.year_month=tmp.year_month
)

--集計した移動年計金額
,tmp4 AS(
    SELECT 
        year_month
        ,SUM(sum_sales_atm) OVER (PARTITION BY STRFTIME("%Y",year_month) ORDER BY year_month
        ROWS BETWEEN 10 PRECEDING AND CURRENT ROW) AS YEAR_MOVE_AMT
    FROM tmp
    GROUP BY 1
)

--集計した月次売上金額と集計した売上累計金額と集計した移動年計金額のマージ
SELECT tmp3.YEAR_MONTH AS YEAR_MONTH
,tmp3.month_amt AS MONTH_AMT
,tmp3.cuml_amt AS  CUML_AMT
,tmp4.YEAR_MOVE_AMT AS YEAR_MOVE_AMT
FROM tmp3
LEFT JOIN tmp4
ON tmp3.year_month = tmp4.year_month
ORDER BY 1;