-- 2023/01/01～2023/12/31
WITH A AS
(
    SELECT
        strftime('%Y-%m', SALES_DATE) AS SALES_YM
    FROM
        SALES
    WHERE
        SALES_DATE BETWEEN '2023-01-01' AND '2023-12-31'
    AND UPDATED_NO IS NULL
    GROUP BY
        strftime('%Y-%m', SALES_DATE)
),
-- 全期間
B AS
(
    SELECT
        strftime('%Y-%m', SALES_DATE) AS SALES_YM,
        SUM(SALES_AMT) AS SUM_SALES_AMT
    FROM
        SALES
    WHERE
        UPDATED_NO IS NULL
    GROUP BY
        strftime('%Y-%m', SALES_DATE)
),
C AS
(
    SELECT
        SUM(CASE WHEN STRFTIME('%Y-%m', SALES_DATE)<='2023-01' THEN SALES_AMT ELSE 0 END) AS AMT1,
        SUM(CASE WHEN STRFTIME('%Y-%m', SALES_DATE)<='2023-02' THEN SALES_AMT ELSE 0 END) AS AMT2,
        SUM(CASE WHEN STRFTIME('%Y-%m', SALES_DATE)<='2023-03' THEN SALES_AMT ELSE 0 END) AS AMT3,
        SUM(CASE WHEN STRFTIME('%Y-%m', SALES_DATE)<='2023-04' THEN SALES_AMT ELSE 0 END) AS AMT4,
        SUM(CASE WHEN STRFTIME('%Y-%m', SALES_DATE)<='2023-05' THEN SALES_AMT ELSE 0 END) AS AMT5,
        SUM(CASE WHEN STRFTIME('%Y-%m', SALES_DATE)<='2023-06' THEN SALES_AMT ELSE 0 END) AS AMT6,
        SUM(CASE WHEN STRFTIME('%Y-%m', SALES_DATE)<='2023-07' THEN SALES_AMT ELSE 0 END) AS AMT7,
        SUM(CASE WHEN STRFTIME('%Y-%m', SALES_DATE)<='2023-08' THEN SALES_AMT ELSE 0 END) AS AMT8,
        SUM(CASE WHEN STRFTIME('%Y-%m', SALES_DATE)<='2023-09' THEN SALES_AMT ELSE 0 END) AS AMT9,
        SUM(CASE WHEN STRFTIME('%Y-%m', SALES_DATE)<='2023-10' THEN SALES_AMT ELSE 0 END) AS AMT10,
        SUM(CASE WHEN STRFTIME('%Y-%m', SALES_DATE)<='2023-11' THEN SALES_AMT ELSE 0 END) AS AMT11,
        SUM(CASE WHEN STRFTIME('%Y-%m', SALES_DATE)<='2023-12' THEN SALES_AMT ELSE 0 END) AS AMT12
    FROM
        SALES
    WHERE
        SALES_DATE BETWEEN '2023-01-01' AND '2023-12-31'
    AND UPDATED_NO IS NULL
)

SELECT
    A.SALES_YM AS YEAR_MONTH,
    B1.SUM_SALES_AMT AS MONTH_AMT,
    CASE A.SALES_YM 
        WHEN '2023-01' THEN C.AMT1
        WHEN '2023-02' THEN C.AMT2
        WHEN '2023-03' THEN C.AMT3
        WHEN '2023-04' THEN C.AMT4
        WHEN '2023-05' THEN C.AMT5
        WHEN '2023-06' THEN C.AMT6
        WHEN '2023-07' THEN C.AMT7
        WHEN '2023-08' THEN C.AMT8
        WHEN '2023-09' THEN C.AMT9
        WHEN '2023-10' THEN C.AMT10
        WHEN '2023-11' THEN C.AMT11
        WHEN '2023-12' THEN C.AMT12
        ELSE 0
    END AS CUML_AMT,
    SUM(X.SUM_SALES_AMT) AS YEAR_MOVE_AMT
FROM
    A,C
    INNER JOIN
        B AS B1
        ON
            B1.SALES_YM = A.SALES_YM

    INNER JOIN
    (
        SELECT
            A2.SALES_YM,
            SUM(B2.SUM_SALES_AMT) AS SUM_SALES_AMT
        FROM A AS A2
        INNER JOIN B AS B2
            ON
                B2.SALES_YM >= strftime('%Y-%m', DATE(A2.SALES_YM || '-01', '-11 month'))
            AND A2.SALES_YM >= B2.SALES_YM
        GROUP BY
            A2.SALES_YM
    ) X
    ON X.SALES_YM=A.SALES_YM
GROUP BY
    A.SALES_YM
ORDER BY
    A.SALES_YM