서브쿼리와 CTE

-- 스칼라 서브쿼리: 평균보다 비싼 상품
SELECT name, price
FROM products
WHERE price > (SELECT AVG(price) FROM products);

-- SELECT 절에서 스칼라 서브쿼리
SELECT
    name,
    price,
    price - (SELECT AVG(price) FROM products) AS diff_from_avg
FROM products
ORDER BY diff_from_avg DESC;

-- 상관 서브쿼리: 각 카테고리의 최고가 상품
SELECT p.name, p.category_id, p.price
FROM products p
WHERE p.price = (
    SELECT MAX(p2.price)
    FROM products p2
    WHERE p2.category_id = p.category_id
);

-- IN: 주문한 적 있는 고객
SELECT name, email
FROM users
WHERE id IN (
    SELECT DISTINCT user_id FROM orders
);

-- NOT IN: 주문한 적 없는 고객
SELECT name, email
FROM users
WHERE id NOT IN (
    SELECT DISTINCT user_id FROM orders
    WHERE user_id IS NOT NULL
);

-- EXISTS: 주문한 적 있는 고객 (대규모 데이터에서 더 효율적)
SELECT u.name, u.email
FROM users u
WHERE EXISTS (
    SELECT 1 FROM orders o WHERE o.user_id = u.id
);

-- NOT EXISTS: 리뷰가 없는 상품
SELECT p.name
FROM products p
WHERE NOT EXISTS (
    SELECT 1 FROM reviews r WHERE r.product_id = p.id
);

-- 기본 CTE: 카테고리별 매출 순위
WITH category_sales AS (
    SELECT
        c.name AS category,
        SUM(oi.quantity * oi.unit_price) AS total_sales
    FROM categories c
    INNER JOIN products p ON c.id = p.category_id
    INNER JOIN order_items oi ON p.id = oi.product_id
    GROUP BY c.name
)
SELECT
    category,
    total_sales,
    RANK() OVER (ORDER BY total_sales DESC) AS sales_rank
FROM category_sales;

-- 여러 CTE 결합
WITH monthly_revenue AS (
    SELECT
        DATE_FORMAT(ordered_at, '%Y-%m') AS month,
        SUM(total_amount) AS revenue
    FROM orders
    WHERE status = 'completed'
    GROUP BY month
),
avg_revenue AS (
    SELECT AVG(revenue) AS avg_rev FROM monthly_revenue
)
SELECT
    m.month,
    m.revenue,
    a.avg_rev,
    CASE WHEN m.revenue > a.avg_rev THEN '상회' ELSE '하회' END AS vs_avg
FROM monthly_revenue m, avg_revenue a
ORDER BY m.month;

-- 재귀 CTE: 조직도 계층 구조
WITH RECURSIVE org_tree AS (
    -- 앵커 멤버: 최상위 (상사 없음)
    SELECT id, name, manager_id, 1 AS depth,
           CAST(name AS CHAR(500)) AS path
    FROM employees
    WHERE manager_id IS NULL

    UNION ALL

    -- 재귀 멤버: 하위 직원
    SELECT e.id, e.name, e.manager_id, t.depth + 1,
           CONCAT(t.path, ' > ', e.name)
    FROM employees e
    INNER JOIN org_tree t ON e.manager_id = t.id
)
SELECT
    CONCAT(REPEAT('  ', depth - 1), name) AS org_chart,
    depth,
    path
FROM org_tree
ORDER BY path;

-- 재귀 CTE: 카테고리 트리
WITH RECURSIVE cat_tree AS (
    SELECT id, name, parent_id, 0 AS level
    FROM categories
    WHERE parent_id IS NULL

    UNION ALL

    SELECT c.id, c.name, c.parent_id, ct.level + 1
    FROM categories c
    INNER JOIN cat_tree ct ON c.parent_id = ct.id
)
SELECT * FROM cat_tree ORDER BY level, name;