Intermediate Comprehensive Exercises

SELECT
    cat.name AS category,
    COUNT(*) AS product_count,
    ROUND(AVG(p.price)) AS avg_price
FROM products p
INNER JOIN categories cat ON p.category_id = cat.id
WHERE p.is_active = 1
GROUP BY cat.id, cat.name
ORDER BY product_count DESC
LIMIT 10;

SELECT
    o.order_number,
    c.name AS customer_name,
    CASE pay.method
        WHEN 'card' THEN '카드'
        WHEN 'bank_transfer' THEN '계좌이체'
        WHEN 'kakao_pay' THEN '카카오페이'
        WHEN 'naver_pay' THEN '네이버페이'
        WHEN 'virtual_account' THEN '가상계좌'
        WHEN 'point' THEN '포인트'
        ELSE '기타'
    END AS payment_method_kr,
    o.total_amount
FROM orders o
INNER JOIN customers c ON o.customer_id = c.id
INNER JOIN payments pay ON o.id = pay.order_id
ORDER BY o.ordered_at DESC
LIMIT 10;

SELECT
    c.name,
    sub.order_count,
    sub.total_spent
FROM (
    SELECT customer_id, COUNT(*) AS order_count, SUM(total_amount) AS total_spent
    FROM orders
    WHERE ordered_at LIKE '2024%'
    GROUP BY customer_id
    ORDER BY order_count DESC
    LIMIT 5
) sub
INNER JOIN customers c ON sub.customer_id = c.id
ORDER BY sub.order_count DESC;

SELECT
    SUBSTR(email, INSTR(email, '@') + 1) AS domain,
    COUNT(*) AS customer_count
FROM customers
GROUP BY domain
ORDER BY customer_count DESC;

SELECT
    c.name,
    COUNT(*) AS review_count,
    ROUND(AVG(r.rating), 1) AS avg_rating
FROM reviews r
INNER JOIN customers c ON r.customer_id = c.id
GROUP BY r.customer_id, c.name
HAVING COUNT(*) >= 5
ORDER BY review_count DESC
LIMIT 10;

SELECT '신규 고객' AS category, COUNT(*) AS count_2024
FROM customers
WHERE created_at LIKE '2024%'
UNION ALL
SELECT '신규 상품' AS category, COUNT(*) AS count_2024
FROM products
WHERE created_at LIKE '2024%';

SELECT
    s.carrier,
    COUNT(*) AS delivered_count,
    ROUND(AVG(julianday(s.delivered_at) - julianday(o.ordered_at)), 1) AS avg_days
FROM shipping s
INNER JOIN orders o ON s.order_id = o.id
WHERE s.status = 'delivered' AND s.delivered_at IS NOT NULL
GROUP BY s.carrier
ORDER BY avg_days;

SELECT
    p.name, p.price, cat_avg.avg_price,
    CASE
        WHEN p.price > cat_avg.avg_price * 1.2 THEN '고가'
        WHEN p.price < cat_avg.avg_price * 0.8 THEN '저가'
        ELSE '평균 수준'
    END AS price_level
FROM products p
INNER JOIN (
    SELECT category_id, ROUND(AVG(price)) AS avg_price
    FROM products WHERE is_active = 1
    GROUP BY category_id
) cat_avg ON p.category_id = cat_avg.category_id
WHERE p.is_active = 1
ORDER BY p.price DESC
LIMIT 15;

SELECT
    p.brand,
    SUM(oi.subtotal) AS total_revenue,
    COUNT(DISTINCT oi.order_id) AS order_count
FROM order_items oi
INNER JOIN products p ON oi.product_id = p.id
GROUP BY p.brand
HAVING SUM(oi.subtotal) > (
    SELECT AVG(brand_revenue)
    FROM (
        SELECT SUM(oi2.subtotal) AS brand_revenue
        FROM order_items oi2
        INNER JOIN products p2 ON oi2.product_id = p2.id
        GROUP BY p2.brand
    )
)
ORDER BY total_revenue DESC
LIMIT 10;

SELECT
    SUBSTR(ordered_at, 1, 7) AS month,
    COUNT(*) AS total_orders,
    SUM(CASE WHEN status = 'confirmed' THEN 1 ELSE 0 END) AS confirmed,
    SUM(CASE WHEN status = 'cancelled' THEN 1 ELSE 0 END) AS cancelled,
    SUM(CASE WHEN status NOT IN ('confirmed', 'cancelled') THEN 1 ELSE 0 END) AS in_progress,
    ROUND(100.0 * SUM(CASE WHEN status = 'cancelled' THEN 1 ELSE 0 END) / COUNT(*), 1) AS cancel_rate
FROM orders
WHERE ordered_at LIKE '2024%'
GROUP BY SUBSTR(ordered_at, 1, 7)
ORDER BY month;

SELECT
    p.name, p.brand, review_avg.avg_rating,
    COUNT(*) AS vip_purchase_count
FROM order_items oi
INNER JOIN orders o ON oi.order_id = o.id
INNER JOIN customers c ON o.customer_id = c.id
INNER JOIN products p ON oi.product_id = p.id
INNER JOIN (
    SELECT product_id, ROUND(AVG(rating), 1) AS avg_rating
    FROM reviews GROUP BY product_id HAVING AVG(rating) >= 4.0
) review_avg ON p.id = review_avg.product_id
WHERE c.grade = 'VIP'
GROUP BY p.id, p.name, p.brand, review_avg.avg_rating
ORDER BY vip_purchase_count DESC
LIMIT 10;

SELECT
    p.name, p.price,
    COALESCE(sales.total_qty, 0) AS total_sold,
    COALESCE(rev.review_count, 0) AS review_count
FROM products p
LEFT JOIN (
    SELECT product_id, SUM(quantity) AS total_qty
    FROM order_items GROUP BY product_id
) sales ON p.id = sales.product_id
LEFT JOIN (
    SELECT product_id, COUNT(*) AS review_count
    FROM reviews GROUP BY product_id
) rev ON p.id = rev.product_id
WHERE p.is_active = 1
ORDER BY total_sold DESC
LIMIT 15;

SELECT
    c.name, c.grade,
    SUM(activity.score) AS total_score,
    SUM(CASE WHEN activity.type = '주문' THEN 1 ELSE 0 END) AS orders,
    SUM(CASE WHEN activity.type = '리뷰' THEN 1 ELSE 0 END) AS reviews,
    SUM(CASE WHEN activity.type = '문의' THEN 1 ELSE 0 END) AS complaints
FROM (
    SELECT customer_id, '주문' AS type, 10 AS score FROM orders
    UNION ALL
    SELECT customer_id, '리뷰' AS type, 5 AS score FROM reviews
    UNION ALL
    SELECT customer_id, '문의' AS type, 3 AS score FROM complaints
) activity
INNER JOIN customers c ON activity.customer_id = c.id
GROUP BY activity.customer_id, c.name, c.grade
ORDER BY total_score DESC
LIMIT 10;

SELECT
    CASE
        WHEN days_to_first_order <= 30 THEN '30일 이내'
        WHEN days_to_first_order <= 90 THEN '31~90일'
        ELSE '90일 초과'
    END AS segment,
    COUNT(*) AS customer_count,
    ROUND(AVG(days_to_first_order), 1) AS avg_days
FROM (
    SELECT c.id,
        ROUND(julianday(MIN(o.ordered_at)) - julianday(c.created_at)) AS days_to_first_order
    FROM customers c
    INNER JOIN orders o ON c.id = o.customer_id
    GROUP BY c.id, c.created_at
)
GROUP BY segment
ORDER BY MIN(days_to_first_order);

SELECT
    CASE pay.method
        WHEN 'card' THEN '카드'
        WHEN 'bank_transfer' THEN '계좌이체'
        WHEN 'kakao_pay' THEN '카카오페이'
        WHEN 'naver_pay' THEN '네이버페이'
        ELSE pay.method
    END AS method_kr,
    COALESCE(pay.card_issuer, '해당없음') AS issuer,
    COUNT(*) AS payment_count,
    SUM(pay.amount) AS total_amount,
    ROUND(AVG(pay.amount)) AS avg_amount
FROM payments pay
WHERE pay.status = 'completed'
GROUP BY pay.method, pay.card_issuer
ORDER BY total_amount DESC
LIMIT 15;

SELECT
    recency_group,
    COUNT(*) AS customer_count,
    ROUND(100.0 * COUNT(*) / SUM(COUNT(*)) OVER(), 1) AS pct
FROM (
    SELECT c.id,
        CASE
            WHEN MAX(o.ordered_at) IS NULL THEN 'Dormant'
            WHEN julianday('now') - julianday(MAX(o.ordered_at)) <= 30 THEN 'Active'
            WHEN julianday('now') - julianday(MAX(o.ordered_at)) <= 90 THEN 'Warm'
            WHEN julianday('now') - julianday(MAX(o.ordered_at)) <= 180 THEN 'Cold'
            ELSE 'Dormant'
        END AS recency_group
    FROM customers c
    LEFT JOIN orders o ON c.id = o.customer_id
    GROUP BY c.id
)
GROUP BY recency_group
ORDER BY CASE recency_group WHEN 'Active' THEN 1 WHEN 'Warm' THEN 2 WHEN 'Cold' THEN 3 ELSE 4 END;

SELECT
    month, total_revenue, order_count, unique_customers,
    ROUND(total_revenue * 1.0 / order_count) AS avg_order_value,
    CASE
        WHEN prev_revenue IS NULL THEN '-'
        ELSE ROUND((total_revenue - prev_revenue) * 100.0 / prev_revenue, 1) || '%'
    END AS growth_rate
FROM (
    SELECT
        SUBSTR(ordered_at, 1, 7) AS month,
        SUM(total_amount) AS total_revenue,
        COUNT(*) AS order_count,
        COUNT(DISTINCT customer_id) AS unique_customers,
        LAG(SUM(total_amount)) OVER (ORDER BY SUBSTR(ordered_at, 1, 7)) AS prev_revenue
    FROM orders
    WHERE ordered_at LIKE '2024%' AND status NOT IN ('cancelled')
    GROUP BY SUBSTR(ordered_at, 1, 7)
)
ORDER BY month;

SELECT
    c.grade,
    COUNT(DISTINCT c.id) AS customer_count,
    ROUND(AVG(order_stats.avg_amount)) AS avg_order_amount,
    ROUND(AVG(review_stats.avg_rating), 2) AS avg_rating,
    ROUND(100.0 * SUM(COALESCE(return_stats.return_count, 0))
        / NULLIF(SUM(COALESCE(order_stats.order_count, 0)), 0), 2) AS return_rate_pct
FROM customers c
LEFT JOIN (
    SELECT customer_id, COUNT(*) AS order_count, AVG(total_amount) AS avg_amount
    FROM orders GROUP BY customer_id
) order_stats ON c.id = order_stats.customer_id
LEFT JOIN (
    SELECT customer_id, AVG(rating) AS avg_rating
    FROM reviews GROUP BY customer_id
) review_stats ON c.id = review_stats.customer_id
LEFT JOIN (
    SELECT customer_id, COUNT(*) AS return_count
    FROM returns GROUP BY customer_id
) return_stats ON c.id = return_stats.customer_id
GROUP BY c.grade
ORDER BY CASE c.grade WHEN 'VIP' THEN 1 WHEN 'GOLD' THEN 2 WHEN 'SILVER' THEN 3 WHEN 'BRONZE' THEN 4 END;

SELECT
    p.name, p.stock_qty AS current_stock,
    COALESCE(sales.qty_30d, 0) AS sold_30d,
    CASE
        WHEN COALESCE(sales.qty_30d, 0) = 0 THEN '판매 없음'
        ELSE CAST(ROUND(p.stock_qty * 30.0 / sales.qty_30d) AS INTEGER) || '일'
    END AS est_days_left
FROM products p
LEFT JOIN (
    SELECT oi.product_id, SUM(oi.quantity) AS qty_30d
    FROM order_items oi
    INNER JOIN orders o ON oi.order_id = o.id
    WHERE o.ordered_at >= date('now', '-30 days') AND o.status NOT IN ('cancelled')
    GROUP BY oi.product_id
) sales ON p.id = sales.product_id
WHERE p.is_active = 1 AND p.stock_qty < COALESCE(sales.qty_30d, 0)
ORDER BY p.stock_qty ASC
LIMIT 15;

SELECT
    top_cat.name AS top_category,
    COUNT(DISTINCT p.id) AS product_count,
    COALESCE(SUM(sales.revenue), 0) AS total_revenue,
    ROUND(AVG(rev.avg_rating), 1) AS avg_rating
FROM categories top_cat
LEFT JOIN categories mid_cat ON mid_cat.parent_id = top_cat.id
LEFT JOIN categories sub_cat ON sub_cat.parent_id = mid_cat.id
LEFT JOIN products p ON p.category_id IN (top_cat.id, mid_cat.id, sub_cat.id)
LEFT JOIN (
    SELECT product_id, SUM(subtotal) AS revenue FROM order_items GROUP BY product_id
) sales ON p.id = sales.product_id
LEFT JOIN (
    SELECT product_id, AVG(rating) AS avg_rating FROM reviews GROUP BY product_id
) rev ON p.id = rev.product_id
WHERE top_cat.depth = 0
GROUP BY top_cat.id, top_cat.name
ORDER BY total_revenue DESC;

SELECT
    s.company_name,
    COUNT(DISTINCT p.id) AS product_count,
    COALESCE(SUM(sales.revenue), 0) AS total_revenue,
    ROUND(AVG(rev.avg_rating), 1) AS avg_rating,
    ROUND(100.0 * COALESCE(SUM(ret.return_count), 0)
        / NULLIF(COALESCE(SUM(sales.sold_count), 0), 0), 2) AS return_rate_pct
FROM suppliers s
INNER JOIN products p ON p.supplier_id = s.id
LEFT JOIN (SELECT product_id, SUM(subtotal) AS revenue, COUNT(*) AS sold_count FROM order_items GROUP BY product_id) sales ON p.id = sales.product_id
LEFT JOIN (SELECT product_id, AVG(rating) AS avg_rating FROM reviews GROUP BY product_id) rev ON p.id = rev.product_id
LEFT JOIN (SELECT oi.product_id, COUNT(DISTINCT r.id) AS return_count FROM returns r INNER JOIN orders o ON r.order_id = o.id INNER JOIN order_items oi ON o.id = oi.order_id GROUP BY oi.product_id) ret ON p.id = ret.product_id
GROUP BY s.id, s.company_name
ORDER BY total_revenue DESC
LIMIT 10;

SELECT
    CASE WHEN cu.id IS NOT NULL THEN '쿠폰 사용' ELSE '쿠폰 미사용' END AS coupon_group,
    COUNT(DISTINCT o.id) AS order_count,
    ROUND(AVG(o.total_amount)) AS avg_amount,
    ROUND(AVG(item_stats.item_count), 1) AS avg_items,
    ROUND(100.0 * SUM(CASE WHEN o.status = 'confirmed' THEN 1 ELSE 0 END)
        / COUNT(DISTINCT o.id), 1) AS confirm_rate_pct
FROM orders o
LEFT JOIN coupon_usage cu ON o.id = cu.order_id
LEFT JOIN (SELECT order_id, COUNT(*) AS item_count FROM order_items GROUP BY order_id) item_stats ON o.id = item_stats.order_id
GROUP BY CASE WHEN cu.id IS NOT NULL THEN '쿠폰 사용' ELSE '쿠폰 미사용' END;

SELECT
    cat.name AS category,
    COUNT(*) AS wishlist_count,
    SUM(w.is_purchased) AS purchased_count,
    ROUND(100.0 * SUM(w.is_purchased) / COUNT(*), 1) AS conversion_rate_pct
FROM wishlists w
INNER JOIN products p ON w.product_id = p.id
INNER JOIN categories cat ON p.category_id = cat.id
GROUP BY cat.id, cat.name
HAVING COUNT(*) >= 5
ORDER BY conversion_rate_pct DESC
LIMIT 10;

SELECT
    category,
    COUNT(*) AS total_count,
    ROUND(AVG(CASE WHEN resolved_at IS NOT NULL THEN julianday(resolved_at) - julianday(created_at) END), 1) AS avg_resolve_days,
    ROUND(100.0 * SUM(CASE WHEN resolved_at IS NOT NULL THEN 1 ELSE 0 END) / COUNT(*), 1) AS resolve_rate_pct,
    ROUND(100.0 * SUM(escalated) / COUNT(*), 1) AS escalation_rate_pct
FROM complaints
GROUP BY category
ORDER BY total_count DESC;

SELECT '총 고객 수' AS metric, CAST(COUNT(*) AS TEXT) AS value FROM customers
UNION ALL
SELECT '활성 고객 수', CAST(COUNT(*) AS TEXT) FROM customers WHERE is_active = 1
UNION ALL
SELECT '이번 달 매출', CAST(COALESCE(SUM(total_amount), 0) AS TEXT) FROM orders WHERE SUBSTR(ordered_at, 1, 7) = strftime('%Y-%m', 'now') AND status NOT IN ('cancelled')
UNION ALL
SELECT '지난 달 매출', CAST(COALESCE(SUM(total_amount), 0) AS TEXT) FROM orders WHERE SUBSTR(ordered_at, 1, 7) = strftime('%Y-%m', 'now', '-1 month') AND status NOT IN ('cancelled')
UNION ALL
SELECT '평균 주문 금액', CAST(ROUND(AVG(total_amount)) AS TEXT) FROM orders WHERE status NOT IN ('cancelled')
UNION ALL
SELECT '평균 리뷰 평점', CAST(ROUND(AVG(rating), 2) AS TEXT) FROM reviews
UNION ALL
SELECT '미해결 문의', CAST(COUNT(*) AS TEXT) FROM complaints WHERE status NOT IN ('resolved', 'closed');