고객/운영 분석

SELECT
    c.id            AS customer_id,
    c.name          AS customer_name,
    c.grade,
    MAX(o.ordered_at)           AS last_order_date,
    COUNT(*)                    AS order_count,
    ROUND(SUM(o.total_amount))  AS total_spent
FROM customers AS c
INNER JOIN orders AS o ON c.id = o.customer_id
WHERE o.status NOT IN ('cancelled', 'returned', 'return_requested')
GROUP BY c.id, c.name, c.grade
ORDER BY total_spent DESC
LIMIT 20;

WITH rfm_raw AS (
    SELECT
        c.id AS customer_id,
        c.name,
        c.grade,
        MAX(o.ordered_at)           AS last_order_date,
        COUNT(*)                    AS frequency,
        ROUND(SUM(o.total_amount))  AS monetary
    FROM customers AS c
    INNER JOIN orders AS o ON c.id = o.customer_id
    WHERE o.status NOT IN ('cancelled', 'returned', 'return_requested')
    GROUP BY c.id, c.name, c.grade
),
rfm_scored AS (
    SELECT
        customer_id,
        name,
        grade,
        last_order_date,
        frequency,
        monetary,
        NTILE(4) OVER (ORDER BY last_order_date ASC)  AS r_score,
        NTILE(4) OVER (ORDER BY frequency ASC)         AS f_score,
        NTILE(4) OVER (ORDER BY monetary ASC)          AS m_score
    FROM rfm_raw
)
SELECT
    customer_id,
    name,
    grade,
    r_score,
    f_score,
    m_score,
    r_score + f_score + m_score AS rfm_total,
    CASE
        WHEN r_score + f_score + m_score >= 10 THEN 'Champion'
        WHEN r_score + f_score + m_score >= 7  THEN 'Loyal'
        WHEN r_score >= 3 AND f_score <= 2     THEN 'New Customer'
        WHEN r_score <= 2 AND f_score >= 3     THEN 'At Risk'
        ELSE 'Regular'
    END AS segment
FROM rfm_scored
ORDER BY rfm_total DESC
LIMIT 20;

WITH first_purchase AS (
    SELECT
        customer_id,
        SUBSTR(MIN(ordered_at), 1, 7) AS cohort_month
    FROM orders
    WHERE status NOT IN ('cancelled', 'returned', 'return_requested')
    GROUP BY customer_id
    HAVING cohort_month LIKE '2023%'
),
monthly_activity AS (
    SELECT DISTINCT
        fp.customer_id,
        fp.cohort_month,
        SUBSTR(o.ordered_at, 1, 7) AS activity_month,
        (CAST(SUBSTR(o.ordered_at, 1, 4) AS INTEGER) - CAST(SUBSTR(fp.cohort_month, 1, 4) AS INTEGER)) * 12
        + CAST(SUBSTR(o.ordered_at, 6, 2) AS INTEGER) - CAST(SUBSTR(fp.cohort_month, 6, 2) AS INTEGER)
            AS month_offset
    FROM first_purchase AS fp
    INNER JOIN orders AS o ON fp.customer_id = o.customer_id
    WHERE o.status NOT IN ('cancelled', 'returned', 'return_requested')
),
cohort_size AS (
    SELECT cohort_month, COUNT(DISTINCT customer_id) AS total_customers
    FROM first_purchase
    GROUP BY cohort_month
)
SELECT
    ma.cohort_month,
    cs.total_customers,
    ma.month_offset,
    COUNT(DISTINCT ma.customer_id) AS active_customers,
    ROUND(100.0 * COUNT(DISTINCT ma.customer_id) / cs.total_customers, 1) AS retention_pct
FROM monthly_activity AS ma
INNER JOIN cohort_size AS cs ON ma.cohort_month = cs.cohort_month
WHERE ma.month_offset BETWEEN 0 AND 6
GROUP BY ma.cohort_month, cs.total_customers, ma.month_offset
ORDER BY ma.cohort_month, ma.month_offset;

WITH customer_activity AS (
    SELECT
        c.id            AS customer_id,
        c.name,
        c.grade,
        MAX(o.ordered_at)           AS last_order_date,
        COUNT(*)                    AS order_count,
        ROUND(AVG(o.total_amount))  AS avg_order_value,
        ROUND(JULIANDAY('2025-03-31') - JULIANDAY(MAX(o.ordered_at))) AS days_since_last
    FROM customers AS c
    INNER JOIN orders AS o ON c.id = o.customer_id
    WHERE o.status NOT IN ('cancelled', 'returned', 'return_requested')
    GROUP BY c.id, c.name, c.grade
)
SELECT
    grade,
    CASE WHEN days_since_last > 180 THEN '이탈 위험' ELSE '활성' END AS status,
    COUNT(*) AS customer_count,
    ROUND(AVG(avg_order_value)) AS avg_order_value,
    ROUND(AVG(order_count), 1)  AS avg_orders,
    ROUND(AVG(days_since_last)) AS avg_days_inactive
FROM customer_activity
GROUP BY grade,
    CASE WHEN days_since_last > 180 THEN '이탈 위험' ELSE '활성' END
ORDER BY
    CASE grade WHEN 'VIP' THEN 1 WHEN 'GOLD' THEN 2 WHEN 'SILVER' THEN 3 ELSE 4 END,
    status;

WITH customer_ltv AS (
    SELECT
        c.id,
        c.grade,
        COUNT(*) AS total_orders,
        AVG(o.total_amount) AS avg_order_value,
        MAX(JULIANDAY(o.ordered_at) - JULIANDAY(MIN(o.ordered_at)) OVER (PARTITION BY c.id))
            / 365.0 AS active_years_raw
    FROM customers AS c
    INNER JOIN orders AS o ON c.id = o.customer_id
    WHERE o.status NOT IN ('cancelled', 'returned', 'return_requested')
    GROUP BY c.id, c.grade
),
ltv_calc AS (
    SELECT
        id,
        grade,
        total_orders,
        avg_order_value,
        MAX(active_years_raw, 1.0) AS active_years,
        total_orders / MAX(active_years_raw, 1.0) AS orders_per_year,
        avg_order_value * (total_orders / MAX(active_years_raw, 1.0)) * MAX(active_years_raw, 1.0)
            AS ltv
    FROM customer_ltv
)
SELECT
    grade,
    COUNT(*) AS customer_count,
    ROUND(AVG(avg_order_value))  AS avg_order_value,
    ROUND(AVG(orders_per_year), 1) AS avg_annual_orders,
    ROUND(AVG(active_years), 1)    AS avg_active_years,
    ROUND(AVG(ltv))                AS avg_ltv
FROM ltv_calc
GROUP BY grade
ORDER BY avg_ltv DESC;

WITH channel_stats AS (
    SELECT
        COALESCE(c.acquisition_channel, '미확인') AS channel,
        c.id AS customer_id,
        c.grade,
        COUNT(o.id) AS order_count,
        COALESCE(SUM(o.total_amount), 0) AS total_spent
    FROM customers AS c
    LEFT JOIN orders AS o
        ON c.id = o.customer_id
        AND o.status NOT IN ('cancelled', 'returned', 'return_requested')
    GROUP BY c.acquisition_channel, c.id, c.grade
)
SELECT
    channel,
    COUNT(*)                        AS customer_count,
    ROUND(AVG(order_count), 1)      AS avg_orders,
    ROUND(AVG(CASE WHEN total_spent > 0 THEN total_spent END)) AS avg_spent,
    ROUND(100.0 * SUM(CASE WHEN grade IN ('VIP', 'GOLD') THEN 1 ELSE 0 END)
        / COUNT(*), 1) AS premium_rate_pct,
    ROUND(100.0 * SUM(CASE WHEN order_count = 0 THEN 1 ELSE 0 END)
        / COUNT(*), 1) AS never_ordered_pct
FROM channel_stats
GROUP BY channel
ORDER BY avg_spent DESC;

WITH grade_order AS (
    SELECT
        customer_id,
        old_grade,
        new_grade,
        changed_at,
        CASE old_grade WHEN 'BRONZE' THEN 1 WHEN 'SILVER' THEN 2 WHEN 'GOLD' THEN 3 WHEN 'VIP' THEN 4 END AS old_rank,
        CASE new_grade WHEN 'BRONZE' THEN 1 WHEN 'SILVER' THEN 2 WHEN 'GOLD' THEN 3 WHEN 'VIP' THEN 4 END AS new_rank
    FROM customer_grade_history
    WHERE changed_at LIKE '2024%'
)
SELECT
    old_grade || ' → ' || new_grade AS grade_change,
    CASE
        WHEN new_rank > old_rank THEN '승급'
        WHEN new_rank < old_rank THEN '강등'
        ELSE '유지'
    END AS direction,
    COUNT(*) AS change_count
FROM grade_order
GROUP BY old_grade, new_grade, direction
ORDER BY
    CASE WHEN new_rank > old_rank THEN 1 WHEN new_rank < old_rank THEN 2 ELSE 3 END,
    change_count DESC;

WITH inventory_value AS (
    SELECT
        p.id,
        p.name,
        p.stock_qty,
        p.cost_price,
        p.stock_qty * p.cost_price AS stock_value
    FROM products AS p
    WHERE p.is_active = 1
      AND p.stock_qty > 0
),
cumulative AS (
    SELECT
        id, name, stock_qty, cost_price, stock_value,
        SUM(stock_value) OVER (ORDER BY stock_value DESC) AS cum_value,
        SUM(stock_value) OVER () AS total_value
    FROM inventory_value
),
classified AS (
    SELECT *,
        CASE
            WHEN 100.0 * cum_value / total_value <= 70 THEN 'A'
            WHEN 100.0 * cum_value / total_value <= 90 THEN 'B'
            ELSE 'C'
        END AS abc_class
    FROM cumulative
)
SELECT
    abc_class,
    COUNT(*)                 AS product_count,
    ROUND(SUM(stock_value)) AS total_stock_value,
    ROUND(100.0 * SUM(stock_value) / (SELECT SUM(stock_value) FROM inventory_value), 1) AS value_pct,
    ROUND(AVG(stock_qty))    AS avg_stock_qty
FROM classified
GROUP BY abc_class
ORDER BY abc_class;

WITH last_sale AS (
    SELECT
        oi.product_id,
        MAX(o.ordered_at) AS last_sold_at
    FROM order_items AS oi
    INNER JOIN orders AS o ON oi.order_id = o.id
    WHERE o.status NOT IN ('cancelled', 'returned', 'return_requested')
    GROUP BY oi.product_id
)
SELECT
    p.name         AS product_name,
    cat.name       AS category,
    p.stock_qty,
    ROUND(p.stock_qty * p.cost_price) AS stock_value,
    ls.last_sold_at,
    ROUND(JULIANDAY('2025-03-31') - JULIANDAY(ls.last_sold_at)) AS days_since_last_sale
FROM products AS p
INNER JOIN categories AS cat ON p.category_id = cat.id
LEFT JOIN last_sale AS ls ON p.id = ls.product_id
WHERE p.is_active = 1
  AND p.stock_qty > 0
  AND (ls.last_sold_at IS NULL OR ls.last_sold_at < DATE('2025-03-31', '-6 months'))
ORDER BY stock_value DESC
LIMIT 20;

WITH daily_demand AS (
    SELECT
        product_id,
        ABS(SUM(quantity)) / 90.0 AS avg_daily_demand
    FROM inventory_transactions
    WHERE type = 'outbound'
      AND created_at >= DATE('2025-03-31', '-3 months')
    GROUP BY product_id
),
reorder_calc AS (
    SELECT
        p.id,
        p.name,
        p.stock_qty,
        ROUND(dd.avg_daily_demand, 2) AS avg_daily_demand,
        ROUND(dd.avg_daily_demand * 7 * 1.5) AS reorder_point,
        ROUND(p.stock_qty / NULLIF(dd.avg_daily_demand, 0)) AS days_of_stock
    FROM products AS p
    INNER JOIN daily_demand AS dd ON p.id = dd.product_id
    WHERE p.is_active = 1
)
SELECT
    name AS product_name,
    stock_qty,
    avg_daily_demand,
    reorder_point,
    days_of_stock,
    CASE
        WHEN stock_qty <= reorder_point THEN '즉시 발주'
        WHEN days_of_stock <= 14 THEN '발주 예정'
        ELSE '충분'
    END AS order_status
FROM reorder_calc
WHERE stock_qty <= reorder_point
ORDER BY days_of_stock ASC;

WITH cogs_2024 AS (
    SELECT
        p.category_id,
        SUM(oi.quantity * p.cost_price) AS annual_cogs
    FROM order_items AS oi
    INNER JOIN orders AS o ON oi.order_id = o.id
    INNER JOIN products AS p ON oi.product_id = p.id
    WHERE o.ordered_at >= '2024-01-01' AND o.ordered_at < '2025-01-01'
      AND o.status NOT IN ('cancelled', 'returned', 'return_requested')
    GROUP BY p.category_id
),
avg_inventory AS (
    SELECT
        category_id,
        AVG(stock_qty * cost_price) AS avg_stock_value,
        SUM(stock_qty * cost_price) AS total_stock_value,
        COUNT(*) AS product_count
    FROM products
    WHERE is_active = 1
    GROUP BY category_id
)
SELECT
    cat.name AS category,
    ai.product_count,
    ROUND(cg.annual_cogs) AS annual_cogs,
    ROUND(ai.total_stock_value) AS current_stock_value,
    ROUND(cg.annual_cogs / NULLIF(ai.avg_stock_value, 0), 1) AS turnover_rate,
    ROUND(365.0 / NULLIF(cg.annual_cogs / NULLIF(ai.avg_stock_value, 0), 0)) AS days_in_inventory
FROM avg_inventory AS ai
INNER JOIN cogs_2024 AS cg ON ai.category_id = cg.category_id
INNER JOIN categories AS cat ON ai.category_id = cat.id
ORDER BY turnover_rate DESC;

WITH monthly_flow AS (
    SELECT
        SUBSTR(created_at, 1, 7) AS year_month,
        SUM(CASE WHEN quantity > 0 THEN quantity ELSE 0 END) AS inbound_qty,
        SUM(CASE WHEN quantity < 0 THEN ABS(quantity) ELSE 0 END) AS outbound_qty,
        SUM(quantity) AS net_change
    FROM inventory_transactions
    WHERE created_at >= '2024-01-01' AND created_at < '2025-01-01'
    GROUP BY SUBSTR(created_at, 1, 7)
)
SELECT
    year_month,
    inbound_qty,
    outbound_qty,
    net_change,
    SUM(net_change) OVER (ORDER BY year_month) AS cumulative_change
FROM monthly_flow
ORDER BY year_month;

WITH resolution_times AS (
    SELECT
        category,
        priority,
        JULIANDAY(resolved_at) - JULIANDAY(created_at) AS resolution_days,
        CASE priority
            WHEN 'urgent' THEN 0.5
            WHEN 'high'   THEN 1.0
            WHEN 'medium' THEN 2.0
            ELSE 3.0
        END AS sla_days
    FROM complaints
    WHERE created_at LIKE '2024%'
      AND resolved_at IS NOT NULL
)
SELECT
    category,
    COUNT(*) AS resolved_count,
    ROUND(AVG(resolution_days), 2) AS avg_resolution_days,
    ROUND(MIN(resolution_days), 2) AS min_days,
    ROUND(MAX(resolution_days), 2) AS max_days,
    ROUND(100.0 * SUM(CASE WHEN resolution_days <= sla_days THEN 1 ELSE 0 END)
        / COUNT(*), 1) AS sla_compliance_pct
FROM resolution_times
GROUP BY category
ORDER BY sla_compliance_pct ASC;

-- 사유별 전체 통계
SELECT
    reason,
    COUNT(*)                     AS return_count,
    ROUND(100.0 * COUNT(*) / SUM(COUNT(*)) OVER (), 1) AS pct,
    ROUND(AVG(refund_amount))    AS avg_refund,
    ROUND(SUM(refund_amount))    AS total_refund
FROM returns
WHERE requested_at >= '2024-01-01' AND requested_at < '2025-01-01'
GROUP BY reason
ORDER BY return_count DESC;

-- 분기별 추이
SELECT
    'Q' || ((CAST(SUBSTR(requested_at, 6, 2) AS INTEGER) + 2) / 3) AS quarter,
    reason,
    COUNT(*) AS return_count
FROM returns
WHERE requested_at >= '2024-01-01' AND requested_at < '2025-01-01'
GROUP BY (CAST(SUBSTR(requested_at, 6, 2) AS INTEGER) + 2) / 3, reason
ORDER BY quarter, return_count DESC;

WITH staff_metrics AS (
    SELECT
        s.name AS staff_name,
        COUNT(*) AS case_count,
        SUM(CASE WHEN comp.status IN ('resolved', 'closed') THEN 1 ELSE 0 END) AS resolved_count,
        AVG(CASE
            WHEN comp.resolved_at IS NOT NULL
            THEN JULIANDAY(comp.resolved_at) - JULIANDAY(comp.created_at)
        END) AS avg_resolution_days,
        100.0 * SUM(CASE WHEN COALESCE(comp.compensation_type, 'none') = 'none' THEN 1 ELSE 0 END)
            / COUNT(*) AS no_compensation_pct
    FROM complaints AS comp
    INNER JOIN staff AS s ON comp.staff_id = s.id
    WHERE comp.created_at LIKE '2024%'
    GROUP BY s.id, s.name
)
SELECT
    staff_name,
    case_count,
    ROUND(100.0 * resolved_count / case_count, 1) AS resolution_rate,
    ROUND(avg_resolution_days, 2) AS avg_days,
    ROUND(no_compensation_pct, 1) AS satisfaction_proxy_pct,
    ROUND(AVG(case_count) OVER (), 1) AS team_avg_cases,
    ROUND(AVG(avg_resolution_days) OVER (), 2) AS team_avg_days
FROM staff_metrics
ORDER BY resolution_rate DESC;

SELECT
    category,
    channel,
    priority,
    COUNT(*) AS total_count,
    SUM(escalated) AS escalated_count,
    ROUND(100.0 * SUM(escalated) / COUNT(*), 1) AS escalation_rate_pct,
    ROUND(AVG(CASE
        WHEN escalated = 1 AND resolved_at IS NOT NULL
        THEN JULIANDAY(resolved_at) - JULIANDAY(created_at)
    END), 2) AS escalated_avg_days
FROM complaints
WHERE created_at LIKE '2024%'
GROUP BY category, channel, priority
HAVING COUNT(*) >= 5
ORDER BY escalation_rate_pct DESC
LIMIT 15;

WITH top_products AS (
    SELECT
        p.id AS product_id,
        p.name AS product_name,
        SUM(oi.quantity) AS total_sold
    FROM order_items AS oi
    INNER JOIN orders AS o ON oi.order_id = o.id
    INNER JOIN products AS p ON oi.product_id = p.id
    WHERE o.status NOT IN ('cancelled')
      AND o.ordered_at >= '2024-01-01' AND o.ordered_at < '2025-01-01'
    GROUP BY p.id, p.name
    ORDER BY total_sold DESC
    LIMIT 30
),
complaint_counts AS (
    SELECT
        oi.product_id,
        COUNT(DISTINCT comp.id) AS complaint_count
    FROM complaints AS comp
    INNER JOIN orders AS o ON comp.order_id = o.id
    INNER JOIN order_items AS oi ON o.id = oi.order_id
    WHERE comp.created_at LIKE '2024%'
    GROUP BY oi.product_id
),
return_counts AS (
    SELECT
        oi.product_id,
        COUNT(DISTINCT ret.id) AS return_count
    FROM returns AS ret
    INNER JOIN order_items AS oi ON ret.order_id = oi.order_id
    WHERE ret.requested_at LIKE '2024%'
    GROUP BY oi.product_id
)
SELECT
    tp.product_name,
    tp.total_sold,
    COALESCE(cc.complaint_count, 0) AS complaints,
    COALESCE(rc.return_count, 0) AS returns,
    COALESCE(cc.complaint_count, 0) + COALESCE(rc.return_count, 0) AS total_cs,
    ROUND(100.0 * (COALESCE(cc.complaint_count, 0) + COALESCE(rc.return_count, 0))
        / tp.total_sold, 1) AS cs_rate_pct
FROM top_products AS tp
LEFT JOIN complaint_counts AS cc ON tp.product_id = cc.product_id
LEFT JOIN return_counts AS rc ON tp.product_id = rc.product_id
ORDER BY cs_rate_pct DESC;

WITH target_day AS (SELECT '2024-12-15' AS d),
day_orders AS (
    SELECT
        COUNT(*) AS order_count,
        ROUND(SUM(total_amount)) AS revenue
    FROM orders, target_day
    WHERE DATE(ordered_at) = d
      AND status NOT IN ('cancelled', 'returned', 'return_requested')
),
prev_orders AS (
    SELECT
        COUNT(*) AS order_count,
        ROUND(SUM(total_amount)) AS revenue
    FROM orders, target_day
    WHERE DATE(ordered_at) = DATE(d, '-1 day')
      AND status NOT IN ('cancelled', 'returned', 'return_requested')
),
new_customers AS (
    SELECT COUNT(*) AS cnt
    FROM customers, target_day
    WHERE DATE(created_at) = d
),
deliveries AS (
    SELECT COUNT(*) AS cnt
    FROM shipping, target_day
    WHERE DATE(delivered_at) = d
),
open_cs AS (
    SELECT COUNT(*) AS cnt
    FROM complaints
    WHERE status = 'open'
)
SELECT
    (SELECT d FROM target_day) AS report_date,
    do.order_count AS today_orders,
    do.revenue AS today_revenue,
    po.order_count AS yesterday_orders,
    po.revenue AS yesterday_revenue,
    ROUND(100.0 * (do.revenue - po.revenue) / NULLIF(po.revenue, 0), 1) AS revenue_change_pct,
    nc.cnt AS new_signups,
    dl.cnt AS deliveries_completed,
    oc.cnt AS open_cs_tickets
FROM day_orders AS do
CROSS JOIN prev_orders AS po
CROSS JOIN new_customers AS nc
CROSS JOIN deliveries AS dl
CROSS JOIN open_cs AS oc;

WITH supplier_products AS (
    SELECT
        s.id AS supplier_id,
        s.company_name,
        COUNT(DISTINCT p.id) AS product_count,
        SUM(p.stock_qty) AS total_stock
    FROM suppliers AS s
    INNER JOIN products AS p ON s.id = p.supplier_id
    WHERE p.is_active = 1
    GROUP BY s.id, s.company_name
),
supplier_sales AS (
    SELECT
        p.supplier_id,
        SUM(oi.quantity) AS total_sold,
        ROUND(SUM(oi.quantity * oi.unit_price)) AS total_revenue
    FROM order_items AS oi
    INNER JOIN orders AS o ON oi.order_id = o.id
    INNER JOIN products AS p ON oi.product_id = p.id
    WHERE o.ordered_at >= '2024-01-01' AND o.ordered_at < '2025-01-01'
      AND o.status NOT IN ('cancelled', 'returned', 'return_requested')
    GROUP BY p.supplier_id
),
supplier_returns AS (
    SELECT
        p.supplier_id,
        COUNT(DISTINCT ret.id) AS return_count
    FROM returns AS ret
    INNER JOIN order_items AS oi ON ret.order_id = oi.order_id
    INNER JOIN products AS p ON oi.product_id = p.id
    WHERE ret.requested_at >= '2024-01-01' AND ret.requested_at < '2025-01-01'
    GROUP BY p.supplier_id
),
supplier_reviews AS (
    SELECT
        p.supplier_id,
        ROUND(AVG(r.rating), 2) AS avg_rating,
        COUNT(*) AS review_count
    FROM reviews AS r
    INNER JOIN products AS p ON r.product_id = p.id
    GROUP BY p.supplier_id
)
SELECT
    sp.company_name,
    sp.product_count,
    COALESCE(ss.total_revenue, 0) AS revenue_2024,
    COALESCE(ss.total_sold, 0) AS units_sold,
    ROUND(100.0 * COALESCE(sr.return_count, 0) / NULLIF(ss.total_sold, 0), 1) AS return_rate_pct,
    COALESCE(srv.avg_rating, 0) AS avg_rating,
    sp.total_stock
FROM supplier_products AS sp
LEFT JOIN supplier_sales AS ss ON sp.supplier_id = ss.supplier_id
LEFT JOIN supplier_returns AS sr ON sp.supplier_id = sr.supplier_id
LEFT JOIN supplier_reviews AS srv ON sp.supplier_id = srv.supplier_id
ORDER BY revenue_2024 DESC;

WITH sales_kpi AS (
    SELECT
        ROUND(SUM(CASE WHEN ordered_at LIKE '2024-12%' THEN total_amount ELSE 0 END)) AS dec_revenue,
        ROUND(SUM(CASE WHEN ordered_at LIKE '2024-11%' THEN total_amount ELSE 0 END)) AS nov_revenue,
        ROUND(SUM(CASE WHEN ordered_at LIKE '2023-12%' THEN total_amount ELSE 0 END)) AS dec_2023_revenue
    FROM orders
    WHERE status NOT IN ('cancelled', 'returned', 'return_requested')
      AND (ordered_at LIKE '2024-12%' OR ordered_at LIKE '2024-11%' OR ordered_at LIKE '2023-12%')
),
customer_kpi AS (
    SELECT
        COUNT(DISTINCT CASE WHEN o.ordered_at LIKE '2024-12%' THEN o.customer_id END) AS active_customers,
        (SELECT COUNT(*) FROM customers WHERE created_at LIKE '2024-12%') AS new_signups,
        ROUND(100.0 * COUNT(DISTINCT CASE
            WHEN o.ordered_at LIKE '2024-12%'
            AND o.customer_id IN (
                SELECT customer_id FROM orders
                WHERE ordered_at < '2024-12-01'
                  AND status NOT IN ('cancelled', 'returned', 'return_requested')
            )
            THEN o.customer_id
        END) / NULLIF(COUNT(DISTINCT CASE WHEN o.ordered_at LIKE '2024-12%' THEN o.customer_id END), 0), 1)
            AS repeat_rate_pct
    FROM orders AS o
    WHERE o.status NOT IN ('cancelled', 'returned', 'return_requested')
),
inventory_kpi AS (
    SELECT
        SUM(CASE WHEN stock_qty = 0 THEN 1 ELSE 0 END) AS out_of_stock_count,
        ROUND(SUM(stock_qty * cost_price)) AS total_stock_value,
        SUM(CASE WHEN stock_qty > 0 AND stock_qty <= 10 THEN 1 ELSE 0 END) AS low_stock_warning
    FROM products
    WHERE is_active = 1
),
cs_kpi AS (
    SELECT
        SUM(CASE WHEN status = 'open' THEN 1 ELSE 0 END) AS open_tickets,
        ROUND(AVG(CASE
            WHEN resolved_at IS NOT NULL AND created_at LIKE '2024-12%'
            THEN JULIANDAY(resolved_at) - JULIANDAY(created_at)
        END), 2) AS avg_resolution_days,
        ROUND(100.0 * SUM(CASE
            WHEN created_at LIKE '2024-12%' AND status IN ('resolved', 'closed') THEN 1 ELSE 0 END)
            / NULLIF(SUM(CASE WHEN created_at LIKE '2024-12%' THEN 1 ELSE 0 END), 0), 1)
            AS resolution_rate_pct
    FROM complaints
)
SELECT
    '2024-12' AS report_month,
    -- 매출
    sk.dec_revenue,
    ROUND(100.0 * (sk.dec_revenue - sk.nov_revenue) / NULLIF(sk.nov_revenue, 0), 1) AS mom_growth_pct,
    ROUND(100.0 * (sk.dec_revenue - sk.dec_2023_revenue) / NULLIF(sk.dec_2023_revenue, 0), 1) AS yoy_growth_pct,
    -- 고객
    ck.active_customers,
    ck.new_signups,
    ck.repeat_rate_pct,
    -- 재고
    ik.out_of_stock_count,
    ik.total_stock_value,
    ik.low_stock_warning,
    -- CS
    csk.open_tickets,
    csk.avg_resolution_days,
    csk.resolution_rate_pct
FROM sales_kpi AS sk
CROSS JOIN customer_kpi AS ck
CROSS JOIN inventory_kpi AS ik
CROSS JOIN cs_kpi AS csk;