SELECT
    strftime('%Y-%m', o.order_date) as month,
    SUM(o.total_amount) as revenue
FROM orders o
JOIN order_items oi ON o.order_id = oi.order_id
GROUP BY strftime('%Y-%m', o.order_date);

orders 테이블 (부모)
┌──────────┬─────────────┐
│ order_id │ total_amount│
├──────────┼─────────────┤
│ 100      │ 50,000      │
└──────────┴─────────────┘

order_items 테이블 (자식) - 주문당 여러 상품
┌──────────┬────────────┬───────────┐
│ order_id │ product_id │ unit_price│
├──────────┼────────────┼───────────┤
│ 100      │ A          │ 20,000    │
│ 100      │ B          │ 15,000    │
│ 100      │ C          │ 15,000    │
└──────────┴────────────┴───────────┘

SELECT o.order_id, o.total_amount, oi.product_id
FROM orders o
JOIN order_items oi ON o.order_id = oi.order_id
WHERE o.order_id = 100;

SELECT SUM(total_amount) FROM ... -- 150,000원 (3배)

-- ✅ 정확한 매출: orders만 사용
SELECT
    strftime('%Y-%m', order_date) as month,
    COUNT(*) as order_count,
    SUM(total_amount) as revenue
FROM orders
WHERE status = 'completed'
GROUP BY strftime('%Y-%m', order_date);

WITH order_summary AS (
    -- orders 레벨 집계 (Row Explosion 없음)
    SELECT
        strftime('%Y-%m', order_date) as month,
        COUNT(*) as order_count,
        COUNT(DISTINCT customer_id) as customer_count,
        SUM(total_amount) as revenue
    FROM orders
    WHERE status = 'completed'
    GROUP BY strftime('%Y-%m', order_date)
),
item_summary AS (
    -- order_items 레벨 집계
    SELECT
        strftime('%Y-%m', o.order_date) as month,
        SUM(oi.quantity) as units_sold,
        COUNT(DISTINCT oi.product_id) as unique_products
    FROM order_items oi
    JOIN orders o ON oi.order_id = o.order_id
    WHERE o.status = 'completed'
    GROUP BY strftime('%Y-%m', o.order_date)
)
SELECT
    os.month,
    os.order_count,
    os.customer_count,
    os.revenue,
    ROUND(os.revenue / os.order_count, 0) as aov,  -- 평균 주문액
    its.units_sold,
    its.unique_products
FROM order_summary os
JOIN item_summary its ON os.month = its.month
ORDER BY os.month;

-- ❌ 모든 주문 포함 (취소도!)
SELECT SUM(total_amount) as revenue FROM orders;

-- ✅ 완료된 주문만
SELECT SUM(total_amount) as revenue
FROM orders
WHERE status = 'completed';

-- ✅ 더 정확하게: 환불 차감
SELECT
    SUM(CASE WHEN status = 'completed' THEN total_amount ELSE 0 END) as gross_revenue,
    SUM(CASE WHEN status = 'refunded' THEN total_amount ELSE 0 END) as refunds,
    SUM(CASE WHEN status = 'completed' THEN total_amount ELSE 0 END) -
    SUM(CASE WHEN status = 'refunded' THEN total_amount ELSE 0 END) as net_revenue
FROM orders;

-- ❌ 할인 무시
SELECT SUM(quantity * unit_price) as revenue FROM order_items;

-- ✅ 할인 포함
SELECT
    SUM(quantity * unit_price) as gross_revenue,
    SUM(discount_amount) as total_discount,
    SUM(quantity * unit_price - discount_amount) as net_revenue
FROM order_items;

-- ❌ UTC 시간 그대로 (한국과 9시간 차이)
SELECT
    strftime('%Y-%m-%d', order_date) as date,
    SUM(total_amount) as revenue
FROM orders
GROUP BY strftime('%Y-%m-%d', order_date);

-- ✅ 한국 시간으로 변환
SELECT
    strftime('%Y-%m-%d', datetime(order_date, '+9 hours')) as date_kst,
    SUM(total_amount) as revenue
FROM orders
GROUP BY strftime('%Y-%m-%d', datetime(order_date, '+9 hours'));

WITH customer_orders AS (
    SELECT
        customer_id,
        COUNT(*) as order_count,
        SUM(total_amount) as total_spent,
        MIN(order_date) as first_order,
        MAX(order_date) as last_order
    FROM orders
    WHERE status = 'completed'
    GROUP BY customer_id
)
SELECT
    COUNT(*) as total_customers,
    SUM(CASE WHEN order_count = 1 THEN 1 ELSE 0 END) as one_time_buyers,
    SUM(CASE WHEN order_count > 1 THEN 1 ELSE 0 END) as repeat_buyers,
    ROUND(100.0 * SUM(CASE WHEN order_count > 1 THEN 1 ELSE 0 END) / COUNT(*), 1) as repurchase_rate,
    ROUND(AVG(order_count), 2) as avg_orders_per_customer,
    ROUND(AVG(total_spent), 0) as avg_ltv
FROM customer_orders;

WITH customer_orders AS (
    SELECT
        c.segment,
        o.customer_id,
        COUNT(*) as order_count,
        SUM(o.total_amount) as total_spent
    FROM orders o
    JOIN customers c ON o.customer_id = c.customer_id
    WHERE o.status = 'completed'
    GROUP BY c.segment, o.customer_id
)
SELECT
    segment,
    COUNT(*) as customers,
    SUM(CASE WHEN order_count > 1 THEN 1 ELSE 0 END) as repeat_buyers,
    ROUND(100.0 * SUM(CASE WHEN order_count > 1 THEN 1 ELSE 0 END) / COUNT(*), 1) as repurchase_rate,
    ROUND(AVG(total_spent), 0) as avg_ltv
FROM customer_orders
GROUP BY segment
ORDER BY repurchase_rate DESC;

WITH monthly_revenue AS (
    SELECT
        strftime('%Y-%m', order_date) as month,
        SUM(total_amount) as revenue,
        COUNT(*) as orders,
        COUNT(DISTINCT customer_id) as customers
    FROM orders
    WHERE status = 'completed'
    GROUP BY strftime('%Y-%m', order_date)
)
SELECT
    month,
    revenue,
    orders,
    LAG(revenue) OVER (ORDER BY month) as prev_month_revenue,
    ROUND((revenue - LAG(revenue) OVER (ORDER BY month)) * 100.0 /
        LAG(revenue) OVER (ORDER BY month), 1) as mom_growth
FROM monthly_revenue
ORDER BY month DESC
LIMIT 6;

-- ❌ 잘못된 방법: orders.total_amount로 카테고리 매출 계산
-- 한 주문에 여러 카테고리가 있으면 total_amount가 중복 집계됨

-- ✅ 올바른 방법: order_items 레벨에서 계산
SELECT
    p.category,
    COUNT(DISTINCT o.order_id) as order_count,
    SUM(oi.quantity) as units_sold,
    SUM(oi.quantity * oi.unit_price - oi.discount_amount) as revenue,
    ROUND(SUM(oi.quantity * oi.unit_price - oi.discount_amount) * 100.0 /
          SUM(SUM(oi.quantity * oi.unit_price - oi.discount_amount)) OVER (), 1) as revenue_share
FROM order_items oi
JOIN orders o ON oi.order_id = o.order_id
JOIN products p ON oi.product_id = p.product_id
WHERE o.status = 'completed'
  AND o.order_date >= DATE('now', '-30 days')
GROUP BY p.category
ORDER BY revenue DESC;

WITH customer_cohort AS (
    -- 고객별 첫 구매월 (코호트 정의)
    SELECT
        customer_id,
        strftime('%Y-%m', MIN(order_date)) as cohort_month
    FROM orders
    WHERE status = 'completed'
    GROUP BY customer_id
),
monthly_activity AS (
    -- 고객별 활동 월
    SELECT DISTINCT
        o.customer_id,
        cc.cohort_month,
        strftime('%Y-%m', o.order_date) as activity_month
    FROM orders o
    JOIN customer_cohort cc ON o.customer_id = cc.customer_id
    WHERE o.status = 'completed'
)
SELECT
    cohort_month,
    COUNT(DISTINCT CASE WHEN activity_month = cohort_month THEN customer_id END) as M0,
    COUNT(DISTINCT CASE WHEN activity_month =
        strftime('%Y-%m', DATE(cohort_month || '-01', '+1 month')) THEN customer_id END) as M1,
    COUNT(DISTINCT CASE WHEN activity_month =
        strftime('%Y-%m', DATE(cohort_month || '-01', '+2 month')) THEN customer_id END) as M2,
    COUNT(DISTINCT CASE WHEN activity_month =
        strftime('%Y-%m', DATE(cohort_month || '-01', '+3 month')) THEN customer_id END) as M3
FROM monthly_activity
WHERE cohort_month >= strftime('%Y-%m', DATE('now', '-6 months'))
GROUP BY cohort_month
ORDER BY cohort_month;