[지표] 전월 대비 증감률

WITH RECURSIVE month_series AS (
    -- 최소~최대 payment_date 범위에서 연월 리스트 생성
    SELECT MIN(DATE_FORMAT(payment_date, '%Y-%m-01')) AS yearmonth
    FROM payment
    UNION ALL
    SELECT DATE_ADD(yearmonth, INTERVAL 1 MONTH)
    FROM month_series
    WHERE yearmonth < (SELECT MAX(DATE_FORMAT(payment_date, '%Y-%m-01')) FROM payment)
), monthly_sales AS (
    -- 월별 총 결제 금액 계산 (구매 내역 없는 달은 0 처리)
    SELECT 
        ms.yearmonth,
        COALESCE(SUM(p.amount), 0) AS total_sales
    FROM month_series ms
    LEFT JOIN payment p 
        ON DATE_FORMAT(p.payment_date, '%Y-%m') = DATE_FORMAT(ms.yearmonth, '%Y-%m')
    GROUP BY ms.yearmonth
)
SELECT 
    ms1.yearmonth,
    ms1.total_sales,
    ms2.yearmonth AS prev_month,
    COALESCE(ms2.total_sales, 0) AS prev_total_sales,
    CASE 
        WHEN ms2.total_sales IS NULL THEN NULL  -- 첫 달은 증감률 계산 X
        WHEN ms2.total_sales = 0 THEN 100.00  -- 직전 월이 0이면 100% 증가
        ELSE ROUND((ms1.total_sales - ms2.total_sales) / ms2.total_sales * 100, 2)
    END AS growth_rate
FROM monthly_sales ms1
LEFT JOIN monthly_sales ms2 
    ON ms2.yearmonth = DATE_FORMAT(DATE_SUB(ms1.yearmonth, INTERVAL 1 MONTH), '%Y-%m-01')
ORDER BY ms1.yearmonth;