Lesson 19: CTEs and Recursive CTEs

-- 고객 생애 가치(LTV) 세그먼트 분류
WITH customer_orders AS (
    SELECT
        customer_id,
        COUNT(*)          AS order_count,
        SUM(total_amount) AS lifetime_value
    FROM orders
    WHERE status NOT IN ('cancelled', 'returned')
    GROUP BY customer_id
),
customer_segments AS (
    SELECT
        co.customer_id,
        c.name,
        c.grade,
        co.order_count,
        co.lifetime_value,
        CASE
            WHEN co.lifetime_value >= 5000 THEN 'Champion'
            WHEN co.lifetime_value >= 2000 THEN 'Loyal'
            WHEN co.lifetime_value >= 500  THEN 'Regular'
            ELSE 'Occasional'
        END AS segment
    FROM customer_orders AS co
    INNER JOIN customers AS c ON co.customer_id = c.id
)
SELECT
    segment,
    COUNT(*)                    AS customer_count,
    ROUND(AVG(lifetime_value), 2) AS avg_ltv,
    ROUND(AVG(order_count), 1)    AS avg_orders
FROM customer_segments
GROUP BY segment
ORDER BY avg_ltv DESC;

-- 회원 등급별 매출 상위 3명
WITH customer_revenue AS (
    SELECT
        c.id,
        c.name,
        c.grade,
        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')
    GROUP BY c.id, c.name, c.grade
),
ranked AS (
    SELECT
        *,
        RANK() OVER (PARTITION BY grade ORDER BY total_spent DESC) AS rnk
    FROM customer_revenue
)
SELECT grade, name, total_spent, rnk
FROM ranked
WHERE rnk <= 3
ORDER BY grade, rnk;

=== "MySQL"
    ```sql
    WITH RECURSIVE months AS (
        SELECT 1 AS month_num
        UNION ALL
        SELECT month_num + 1
        FROM months
        WHERE month_num < 12
    )
    SELECT
        m.month_num,
        CONCAT('2024-', LPAD(m.month_num, 2, '0')) AS year_month,
        COUNT(o.id) AS order_count
    FROM months AS m
    LEFT JOIN orders AS o
        ON DATE_FORMAT(o.ordered_at, '%Y-%m') = CONCAT('2024-', LPAD(m.month_num, 2, '0'))
        AND o.status NOT IN ('cancelled', 'returned')
    GROUP BY m.month_num
    ORDER BY m.month_num;
    ```

=== "PostgreSQL"
    ```sql
    WITH RECURSIVE months AS (
        SELECT 1 AS month_num
        UNION ALL
        SELECT month_num + 1
        FROM months
        WHERE month_num < 12
    )
    SELECT
        m.month_num,
        '2024-' || LPAD(m.month_num::text, 2, '0') AS year_month,
        COUNT(o.id) AS order_count
    FROM months AS m
    LEFT JOIN orders AS o
        ON TO_CHAR(o.ordered_at, 'YYYY-MM') = '2024-' || LPAD(m.month_num::text, 2, '0')
        AND o.status NOT IN ('cancelled', 'returned')
    GROUP BY m.month_num
    ORDER BY m.month_num;
    ```

=== "MySQL"
    ```sql
    WITH customer_recency AS (
        SELECT
            customer_id,
            COUNT(*)        AS order_count,
            MAX(ordered_at) AS last_order_date
        FROM orders
        WHERE status NOT IN ('cancelled', 'returned')
        GROUP BY customer_id
    )
    SELECT
        c.id    AS customer_id,
        c.name,
        c.grade,
        cr.order_count,
        cr.last_order_date
    FROM customer_recency AS cr
    INNER JOIN customers AS c ON cr.customer_id = c.id
    WHERE cr.order_count >= 3
      AND DATEDIFF(NOW(), cr.last_order_date) > 180
    ORDER BY cr.last_order_date ASC;
    ```

=== "PostgreSQL"
    ```sql
    WITH customer_recency AS (
        SELECT
            customer_id,
            COUNT(*)        AS order_count,
            MAX(ordered_at) AS last_order_date
        FROM orders
        WHERE status NOT IN ('cancelled', 'returned')
        GROUP BY customer_id
    )
    SELECT
        c.id    AS customer_id,
        c.name,
        c.grade,
        cr.order_count,
        cr.last_order_date
    FROM customer_recency AS cr
    INNER JOIN customers AS c ON cr.customer_id = c.id
    WHERE cr.order_count >= 3
      AND CURRENT_DATE - cr.last_order_date::date > 180
    ORDER BY cr.last_order_date ASC;
    ```

=== "MySQL"
    ```sql
    WITH monthly AS (
        SELECT
            DATE_FORMAT(ordered_at, '%Y-%m') AS year_month,
            SUM(total_amount)                AS revenue
        FROM orders
        WHERE ordered_at >= '2024-01-01'
          AND ordered_at <  '2025-01-01'
          AND status NOT IN ('cancelled', 'returned')
        GROUP BY DATE_FORMAT(ordered_at, '%Y-%m')
    ),
    with_lag AS (
        SELECT
            year_month,
            revenue,
            LAG(revenue) OVER (ORDER BY year_month) AS prev_revenue
        FROM monthly
    )
    SELECT
        year_month,
        revenue,
        prev_revenue,
        ROUND(revenue - prev_revenue, 2) AS mom_change,
        ROUND(100.0 * (revenue - prev_revenue) / prev_revenue, 1) AS mom_pct
    FROM with_lag
    ORDER BY year_month;
    ```

=== "PostgreSQL"
    ```sql
    WITH monthly AS (
        SELECT
            TO_CHAR(ordered_at, 'YYYY-MM') AS year_month,
            SUM(total_amount)              AS revenue
        FROM orders
        WHERE ordered_at >= '2024-01-01'
          AND ordered_at <  '2025-01-01'
          AND status NOT IN ('cancelled', 'returned')
        GROUP BY TO_CHAR(ordered_at, 'YYYY-MM')
    ),
    with_lag AS (
        SELECT
            year_month,
            revenue,
            LAG(revenue) OVER (ORDER BY year_month) AS prev_revenue
        FROM monthly
    )
    SELECT
        year_month,
        revenue,
        prev_revenue,
        ROUND(revenue - prev_revenue, 2) AS mom_change,
        ROUND(100.0 * (revenue - prev_revenue) / prev_revenue, 1) AS mom_pct
    FROM with_lag
    ORDER BY year_month;
    ```

=== "MySQL"
    ```sql
    WITH RECURSIVE category_tree AS (
        SELECT
            id,
            name,
            parent_id,
            name AS full_path
        FROM categories
        WHERE parent_id IS NULL

        UNION ALL

        SELECT
            c.id,
            c.name,
            c.parent_id,
            CONCAT(ct.full_path, ' > ', c.name)
        FROM categories AS c
        INNER JOIN category_tree AS ct ON c.parent_id = ct.id
    )
    SELECT
        ct.id   AS category_id,
        ct.name AS category_name,
        ct.full_path
    FROM category_tree AS ct
    WHERE ct.id NOT IN (SELECT parent_id FROM categories WHERE parent_id IS NOT NULL)
    ORDER BY ct.full_path;
    ```

=== "MySQL"
    ```sql
    WITH RECURSIVE tree AS (
        SELECT id, name AS root_name, id AS root_id
        FROM categories
        WHERE parent_id IS NULL

        UNION ALL

        SELECT c.id, t.root_name, t.root_id
        FROM categories AS c
        INNER JOIN tree AS t ON c.parent_id = t.id
    )
    SELECT
        t.root_name AS root_category,
        COUNT(DISTINCT t.id) - 1 AS subcategory_count,
        COUNT(DISTINCT p.id)     AS product_count
    FROM tree AS t
    LEFT JOIN products AS p ON p.category_id = t.id
    GROUP BY t.root_id, t.root_name
    ORDER BY product_count DESC;
    ```

=== "MySQL"
    ```sql
    WITH RECURSIVE org AS (
        SELECT id, name, manager_id, 0 AS level
        FROM staff
        WHERE manager_id IS NULL

        UNION ALL

        SELECT s.id, s.name, s.manager_id, o.level + 1
        FROM staff AS s
        INNER JOIN org AS o ON s.manager_id = o.id
    )
    SELECT
        m.name AS manager_name,
        m.level,
        COUNT(s.id) AS direct_reports
    FROM org AS m
    LEFT JOIN staff AS s ON s.manager_id = m.id
    GROUP BY m.id, m.name, m.level
    HAVING COUNT(s.id) > 0
    ORDER BY m.level, direct_reports DESC;
    ```

=== "MySQL"
    ```sql
    WITH method_monthly AS (
        SELECT
            DATE_FORMAT(p.paid_at, '%Y-%m') AS year_month,
            p.method,
            SUM(p.amount) AS amount
        FROM payments AS p
        INNER JOIN orders AS o ON p.order_id = o.id
        WHERE p.paid_at >= '2024-01-01'
          AND p.paid_at <  '2025-01-01'
          AND p.status = 'completed'
        GROUP BY DATE_FORMAT(p.paid_at, '%Y-%m'), p.method
    ),
    monthly_total AS (
        SELECT
            year_month,
            SUM(amount) AS total
        FROM method_monthly
        GROUP BY year_month
    )
    SELECT
        mm.year_month,
        mm.method,
        mm.amount,
        mt.total AS monthly_total,
        ROUND(100.0 * mm.amount / mt.total, 1) AS pct
    FROM method_monthly AS mm
    INNER JOIN monthly_total AS mt ON mm.year_month = mt.year_month
    ORDER BY mm.year_month, mm.amount DESC;
    ```

=== "PostgreSQL"
    ```sql
    WITH method_monthly AS (
        SELECT
            TO_CHAR(p.paid_at, 'YYYY-MM') AS year_month,
            p.method,
            SUM(p.amount) AS amount
        FROM payments AS p
        INNER JOIN orders AS o ON p.order_id = o.id
        WHERE p.paid_at >= '2024-01-01'
          AND p.paid_at <  '2025-01-01'
          AND p.status = 'completed'
        GROUP BY TO_CHAR(p.paid_at, 'YYYY-MM'), p.method
    ),
    monthly_total AS (
        SELECT
            year_month,
            SUM(amount) AS total
        FROM method_monthly
        GROUP BY year_month
    )
    SELECT
        mm.year_month,
        mm.method,
        mm.amount,
        mt.total AS monthly_total,
        ROUND(100.0 * mm.amount / mt.total, 1) AS pct
    FROM method_monthly AS mm
    INNER JOIN monthly_total AS mt ON mm.year_month = mt.year_month
    ORDER BY mm.year_month, mm.amount DESC;
    ```