[지표] 월별 지표 - 어려움

WITH RECURSIVE month_series AS (
    -- 1️⃣ 최소~최대 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_customers AS (
    -- 2️⃣ 월별 구매 고객 수 계산 (구매 내역 없는 달은 0 처리)
    SELECT 
        ms.yearmonth,
        COALESCE(COUNT(DISTINCT p.customer_id), 0) AS customer_count
    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
), max_last_3_months AS (
    -- 3️⃣ 각 월별 과거 3개월 중 고객 수가 가장 많았던 연월과 고객 수 찾기
    SELECT 
        mc1.yearmonth,
        (
            SELECT mc2.yearmonth 
            FROM monthly_customers mc2 
            WHERE mc2.yearmonth < mc1.yearmonth
                  AND mc2.yearmonth >= DATE_FORMAT(DATE_SUB(mc1.yearmonth, INTERVAL 3 MONTH), '%Y-%m')
            ORDER BY mc2.customer_count DESC, mc2.yearmonth DESC
            LIMIT 1
        ) AS yearmonth_last3month_max,
        (
            SELECT MAX(mc2.customer_count)
            FROM monthly_customers mc2
            WHERE mc2.yearmonth < mc1.yearmonth
                  AND mc2.yearmonth >= DATE_FORMAT(DATE_SUB(mc1.yearmonth, INTERVAL 3 MONTH), '%Y-%m')
        ) AS customer_count_last3month_max
    FROM monthly_customers mc1
)
SELECT 
    mc.yearmonth,
    mc.customer_count,
    COALESCE(ml3.yearmonth_last3month_max, NULL) AS yearmonth_last3month_max,
    COALESCE(ml3.customer_count_last3month_max, 0) AS customer_count_last3month_max,
    CASE 
        WHEN ml3.customer_count_last3month_max = 0 THEN NULL
        ELSE ROUND((mc.customer_count - ml3.customer_count_last3month_max) / ml3.customer_count_last3month_max * 100, 2)
    END AS growth_rate
FROM monthly_customers mc
LEFT JOIN max_last_3_months ml3 
    ON mc.yearmonth = ml3.yearmonth
ORDER BY mc.yearmonth;