Lesson 21: SELF JOIN and CROSS JOIN

-- 카테고리의 부모-자식 관계 조회
SELECT
    child.id,
    child.name       AS category,
    child.depth,
    parent.name      AS parent_category
FROM categories AS child
LEFT JOIN categories AS parent ON child.parent_id = parent.id
ORDER BY child.depth, child.sort_order;

-- 같은 카테고리에서 가격 차이가 가장 큰 상품 쌍 찾기
SELECT
    p1.name AS product_a,
    p2.name AS product_b,
    p1.price AS price_a,
    p2.price AS price_b,
    ABS(p1.price - p2.price) AS price_diff
FROM products AS p1
INNER JOIN products AS p2
    ON p1.category_id = p2.category_id
   AND p1.id < p2.id  -- 중복 쌍 방지 (A-B만, B-A는 제외)
ORDER BY price_diff DESC
LIMIT 10;

SELECT
    s.name AS employee,
    s.department,
    s.role,
    m.name AS manager
FROM staff s
LEFT JOIN staff m ON s.manager_id = m.id
ORDER BY s.id;

SELECT
    old.name AS discontinued_product,
    old.discontinued_at,
    new.name AS successor_product,
    new.price AS new_price
FROM products old
JOIN products new ON old.successor_id = new.id
WHERE old.successor_id IS NOT NULL
ORDER BY old.discontinued_at;

SELECT
    q.id AS question_id,
    q.content AS question,
    a.content AS answer,
    a.created_at AS answered_at
FROM product_qna q
LEFT JOIN product_qna a ON a.parent_id = q.id
WHERE q.parent_id IS NULL  -- top-level questions only
ORDER BY q.created_at DESC
LIMIT 10;

-- 각 결제 수단이 전체 매출에서 차지하는 비율
SELECT
    p.method,
    COUNT(*)              AS tx_count,
    ROUND(SUM(p.amount), 2) AS total_amount,
    ROUND(100.0 * SUM(p.amount) / gt.grand_total, 1) AS pct
FROM payments AS p
CROSS JOIN (
    SELECT SUM(amount) AS grand_total
    FROM payments
    WHERE status = 'completed'
) AS gt
WHERE p.status = 'completed'
GROUP BY p.method, gt.grand_total
ORDER BY total_amount DESC;

SELECT
    c.date_key,
    c.day_name,
    c.is_weekend,
    c.is_holiday,
    c.holiday_name
FROM calendar c
LEFT JOIN orders o ON DATE(o.ordered_at) = c.date_key
WHERE o.id IS NULL
  AND c.year >= 2024
ORDER BY c.date_key;

=== "MySQL"
    ```sql
    WITH quarters AS (
        SELECT 'Q1' AS q, 1 AS start_m, 3 AS end_m
        UNION ALL SELECT 'Q2', 4, 6
        UNION ALL SELECT 'Q3', 7, 9
        UNION ALL SELECT 'Q4', 10, 12
    ),
    methods AS (
        SELECT DISTINCT method FROM payments
    ),
    quarterly_payments AS (
        SELECT
            CASE
                WHEN MONTH(paid_at) BETWEEN 1 AND 3 THEN 'Q1'
                WHEN MONTH(paid_at) BETWEEN 4 AND 6 THEN 'Q2'
                WHEN MONTH(paid_at) BETWEEN 7 AND 9 THEN 'Q3'
                ELSE 'Q4'
            END AS q,
            method,
            SUM(amount) AS total_amount
        FROM payments
        WHERE status = 'completed'
          AND paid_at >= '2024-01-01'
          AND paid_at <  '2025-01-01'
        GROUP BY q, method
    )
    SELECT
        qr.q,
        m.method,
        COALESCE(ROUND(qp.total_amount, 2), 0) AS total_amount
    FROM quarters AS qr
    CROSS JOIN methods AS m
    LEFT JOIN quarterly_payments AS qp
        ON qr.q = qp.q AND m.method = qp.method
    ORDER BY qr.q, m.method;
    ```

=== "PostgreSQL"
    ```sql
    WITH quarters AS (
        SELECT 'Q1' AS q, 1 AS start_m, 3 AS end_m
        UNION ALL SELECT 'Q2', 4, 6
        UNION ALL SELECT 'Q3', 7, 9
        UNION ALL SELECT 'Q4', 10, 12
    ),
    methods AS (
        SELECT DISTINCT method FROM payments
    ),
    quarterly_payments AS (
        SELECT
            CASE
                WHEN EXTRACT(MONTH FROM paid_at) BETWEEN 1 AND 3 THEN 'Q1'
                WHEN EXTRACT(MONTH FROM paid_at) BETWEEN 4 AND 6 THEN 'Q2'
                WHEN EXTRACT(MONTH FROM paid_at) BETWEEN 7 AND 9 THEN 'Q3'
                ELSE 'Q4'
            END AS q,
            method,
            SUM(amount) AS total_amount
        FROM payments
        WHERE status = 'completed'
          AND paid_at >= '2024-01-01'
          AND paid_at <  '2025-01-01'
        GROUP BY q, method
    )
    SELECT
        qr.q,
        m.method,
        COALESCE(ROUND(qp.total_amount, 2), 0) AS total_amount
    FROM quarters AS qr
    CROSS JOIN methods AS m
    LEFT JOIN quarterly_payments AS qp
        ON qr.q = qp.q AND m.method = qp.method
    ORDER BY qr.q, m.method;
    ```

=== "MySQL"
    ```sql
    WITH months AS (
        SELECT '2024-01' AS m UNION ALL SELECT '2024-02'
        UNION ALL SELECT '2024-03' UNION ALL SELECT '2024-04'
        UNION ALL SELECT '2024-05' UNION ALL SELECT '2024-06'
        UNION ALL SELECT '2024-07' UNION ALL SELECT '2024-08'
        UNION ALL SELECT '2024-09' UNION ALL SELECT '2024-10'
        UNION ALL SELECT '2024-11' UNION ALL SELECT '2024-12'
    ),
    supplier_inbound AS (
        SELECT
            DATE_FORMAT(it.created_at, '%Y-%m') AS year_month,
            p.supplier_id,
            SUM(it.quantity) AS inbound_qty
        FROM inventory_transactions AS it
        INNER JOIN products AS p ON it.product_id = p.id
        WHERE it.type = 'inbound'
          AND it.created_at >= '2024-01-01'
          AND it.created_at <  '2025-01-01'
        GROUP BY DATE_FORMAT(it.created_at, '%Y-%m'), p.supplier_id
    )
    SELECT
        m.m AS year_month,
        s.company_name AS supplier,
        COALESCE(si.inbound_qty, 0) AS inbound_qty
    FROM months AS m
    CROSS JOIN suppliers AS s
    LEFT JOIN supplier_inbound AS si
        ON m.m = si.year_month AND s.id = si.supplier_id
    ORDER BY m.m, s.company_name
    LIMIT 30;
    ```

=== "PostgreSQL"
    ```sql
    WITH months AS (
        SELECT '2024-01' AS m UNION ALL SELECT '2024-02'
        UNION ALL SELECT '2024-03' UNION ALL SELECT '2024-04'
        UNION ALL SELECT '2024-05' UNION ALL SELECT '2024-06'
        UNION ALL SELECT '2024-07' UNION ALL SELECT '2024-08'
        UNION ALL SELECT '2024-09' UNION ALL SELECT '2024-10'
        UNION ALL SELECT '2024-11' UNION ALL SELECT '2024-12'
    ),
    supplier_inbound AS (
        SELECT
            TO_CHAR(it.created_at, 'YYYY-MM') AS year_month,
            p.supplier_id,
            SUM(it.quantity) AS inbound_qty
        FROM inventory_transactions AS it
        INNER JOIN products AS p ON it.product_id = p.id
        WHERE it.type = 'inbound'
          AND it.created_at >= '2024-01-01'
          AND it.created_at <  '2025-01-01'
        GROUP BY TO_CHAR(it.created_at, 'YYYY-MM'), p.supplier_id
    )
    SELECT
        m.m AS year_month,
        s.company_name AS supplier,
        COALESCE(si.inbound_qty, 0) AS inbound_qty
    FROM months AS m
    CROSS JOIN suppliers AS s
    LEFT JOIN supplier_inbound AS si
        ON m.m = si.year_month AND s.id = si.supplier_id
    ORDER BY m.m, s.company_name
    LIMIT 30;
    ```