WITH next_month AS
(
SELECT
    MONTHLY,
    NEW_MRR,EXPANSION_MRR,DOWNGRADE_MRR,CHURN_MRR
FROM
    MRR_DATA
UNION
SELECT
    '2023-01-01' AS MONTHLY,
    0 AS NEW_MRR,
    0 AS EXPANSION_MRR,
    0 AS DOWNGRADE_MRR,
    0 AS CHURN_MRR
    
)


    -- CASE WHEN MONTHLY = '2022-12-01' THEN '2023-01-01'
    -- WHEN MONTHLY = '2022-11-01' THEN '2022-12-01'
    -- WHEN MONTHLY = '2022-10-01' THEN '2022-11-01'
    -- WHEN MONTHLY = '2022-09-01' THEN '2022-10-01'
    -- WHEN MONTHLY = '2022-08-01' THEN '2022-09-01'
    -- WHEN MONTHLY = '2022-07-01' THEN '2022-08-01'
    -- WHEN MONTHLY = '2022-06-01' THEN '2022-07-01'
    -- WHEN MONTHLY = '2022-05-01' THEN '2022-06-01'
    -- WHEN MONTHLY = '2022-04-01' THEN '2022-05-01'
    -- WHEN MONTHLY = '2022-03-01' THEN '2022-04-01'
    -- WHEN MONTHLY = '2022-02-01' THEN '2022-03-01'
    -- WHEN MONTHLY = '2022-01-01' THEN '2022-02-01'
    -- END AS YM,
, monthly_lag_mrr AS
(
SELECT
    -- YM,
    MONTHLY,
    COALESCE(LAG(NEW_MRR,1) OVER(ORDER BY MONTHLY),0) AS lag_new,
    COALESCE(LAG(EXPANSION_MRR,1) OVER(ORDER BY MONTHLY),0) AS lag_exp,
    COALESCE(LAG(DOWNGRADE_MRR,1) OVER(ORDER BY MONTHLY),0) AS lag_down,
    COALESCE(LAG(CHURN_MRR,1) OVER(ORDER BY MONTHLY),0) AS lag_churn
FROM
    next_month
)

    --MRR = 前月のMRR + 前月のNEW_MRR + 前月のEXPANSION_MRR - 前月のDOWNGRADE_MRR - 前月のCHURN_MRR
,cal AS
(
SELECT
    MONTHLY,
    lag_new + lag_exp - lag_down - lag_churn AS cal

FROM
    monthly_lag_mrr
)

SELECT
    MONTHLY,
    cal,
    SUM(cal) OVER(ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS MRR
FROM
    cal