WITH cte_name AS (
    -- CTEクエリ
    SELECT column1, column2 FROM table1
)
-- メインクエリがCTEを参照
SELECT * FROM cte_name;

WITH RECURSIVE cte_name AS (
    -- アンカー：初期クエリ
    SELECT ... FROM table WHERE condition
    UNION ALL
    -- 再帰：自分自身を参照
    SELECT ... FROM table JOIN cte_name ON condition
)
SELECT * FROM cte_name;

CREATE TEMPORARY TABLE temp_result (
    id INT,
    name VARCHAR(50),
    amount DECIMAL(10,2)
);

INSERT INTO temp_result SELECT ...;
SELECT * FROM temp_result;

-- 従業員の全上司を見つける（上方向に走査）
-- 部門の全下位部門を見つける（下方向に走査）
-- 日付シリーズ（数値シリーズ）を生成

-- 基本的なCTE
WITH cte_name AS (
    SELECT ...
)
SELECT ... FROM cte_name;

-- 複数のCTE
WITH cte1 AS (...),
     cte2 AS (...)
SELECT ... FROM cte1 JOIN cte2 ON ...;

-- 再帰的CTE
WITH RECURSIVE cte_name AS (
    -- アンカー（開始点）
    SELECT ... FROM table WHERE initial_condition
    UNION ALL
    -- 再帰（自分自身を参照）
    SELECT ... FROM table 
    JOIN cte_name ON join_condition
    WHERE termination_condition
)
SELECT * FROM cte_name;

-- 一時テーブル
CREATE TEMPORARY TABLE temp_name AS
SELECT ... FROM ...;

-- または手動で作成
CREATE TEMPORARY TABLE temp_name (
    col1 INT,
    col2 VARCHAR(50)
);

WITH dept_stats AS (
    SELECT 
        d.department_id,
        d.department_name AS department,
        COUNT(DISTINCT e.employee_id) AS employee_count,
        COUNT(o.order_id) AS order_count,
        COALESCE(SUM(o.total_amount), 0) AS total_sales
    FROM departments d
    LEFT JOIN employees e ON d.department_id = e.department_id
    LEFT JOIN orders o ON e.employee_id = o.employee_id AND o.status != 'cancelled'
    GROUP BY d.department_id, d.department_name
)
SELECT 
    department,
    employee_count,
    order_count,
    total_sales,
    CASE WHEN employee_count > 0 THEN ROUND(total_sales / employee_count, 2) ELSE 0 END AS sales_per_capita,
    CASE WHEN employee_count > 0 THEN ROUND(order_count * 1.0 / employee_count, 2) ELSE 0 END AS orders_per_capita
FROM dept_stats
ORDER BY sales_per_capita DESC;

department | employee_count | order_count | total_sales | sales_per_capita | orders_per_capita
-----------+----------------+-------------+-------------+------------------+------------------
Sales      |              2 |           4 |    15100.00 |          7550.00 |              2.00
Tech       |              2 |           3 |     8750.00 |          4375.00 |              1.50
Marketing  |              1 |           0 |        0.00 |             0.00 |              0.00
Finance    |              0 |           0 |        0.00 |             0.00 |              0.00

-- まずmanager_idカラムとデータを追加
ALTER TABLE employees ADD COLUMN manager_id INT;

UPDATE employees SET manager_id = NULL WHERE employee_id = 1;  -- Zhang Wei：社長
UPDATE employees SET manager_id = 1    WHERE employee_id = 2;  -- Li Na -> Zhang Wei
UPDATE employees SET manager_id = 1    WHERE employee_id = 3;  -- Wang Qiang -> Zhang Wei
UPDATE employees SET manager_id = 3    WHERE employee_id = 4;  -- Zhao Min -> Wang Qiang
UPDATE employees SET manager_id = 3    WHERE employee_id = 5;  -- Liu Yang -> Wang Qiang
UPDATE employees SET manager_id = NULL WHERE employee_id = 6;  -- Chen Jing：上司なし

-- 再帰クエリ：Zhang Weiから始め、全部下を見つける（下方向に走査）
WITH RECURSIVE emp_hierarchy AS (
    -- アンカー：開始点（Zhang Wei）
    SELECT 
        employee_id,
        CONCAT(first_name, last_name) AS name,
        manager_id,
        0 AS level,
        CAST(CONCAT(first_name, last_name) AS CHAR(500)) AS path
    FROM employees
    WHERE manager_id IS NULL

    UNION ALL

    -- 再帰：部下を見つける
    SELECT 
        e.employee_id,
        CONCAT(e.first_name, e.last_name),
        e.manager_id,
        h.level + 1,
        CAST(CONCAT(h.path, ' → ', e.first_name, e.last_name) AS CHAR(500))
    FROM employees e
    INNER JOIN emp_hierarchy h ON e.manager_id = h.employee_id
)
SELECT 
    CONCAT(REPEAT('  ', level), name) AS org_chart,
    level,
    path
FROM emp_hierarchy
ORDER BY path;

org_chart      | level | path
---------------+-------+----------------------------
Zhang Wei      |     0 | Zhang Wei
  Li Na        |     1 | Zhang Wei → Li Na
  Wang Qiang   |     1 | Zhang Wei → Wang Qiang
    Zhao Min   |     2 | Zhang Wei → Wang Qiang → Zhao Min
    Liu Yang   |     2 | Zhang Wei → Wang Qiang → Liu Yang
Chen Jing      |     0 | Chen Jing

WITH order_summary AS (
    -- ステップ1：各顧客の注文データを集計
    SELECT 
        customer_id,
        MIN(order_date) AS first_purchase,
        MAX(order_date) AS last_purchase,
        COUNT(order_id) AS order_count,
        SUM(total_amount) AS total_spent
    FROM orders
    WHERE status != 'cancelled'
    GROUP BY customer_id
),
customer_ltv AS (
    -- ステップ2：生涯日数と支出頻度を計算
    SELECT 
        customer_id,
        first_purchase,
        last_purchase,
        order_count,
        total_spent,
        DATEDIFF(last_purchase, first_purchase) AS lifetime_days,
        CASE 
            WHEN DATEDIFF(last_purchase, first_purchase) > 0 
            THEN ROUND(total_spent / (DATEDIFF(last_purchase, first_purchase) / 30.0), 2)
            ELSE total_spent
        END AS monthly_avg_spent
    FROM order_summary
)
SELECT 
    customer_id,
    first_purchase,
    last_purchase,
    order_count,
    total_spent,
    lifetime_days,
    monthly_avg_spent,
    CASE
        WHEN monthly_avg_spent >= 3000 THEN '高額顧客'
        WHEN monthly_avg_spent >= 1000 THEN '中額顧客'
        ELSE '低額顧客'
    END AS customer_tier
FROM customer_ltv
ORDER BY total_spent DESC;

WITH RECURSIVE date_series AS (
    -- アンカー：開始日
    SELECT CAST('2026-01-01' AS DATE) AS dt
    UNION ALL
    -- 再帰：毎回1日追加
    SELECT DATE_ADD(dt, INTERVAL 1 DAY)
    FROM date_series
    WHERE dt < '2026-06-30'
)
SELECT 
    ds.dt AS date,
    COALESCE(COUNT(o.order_id), 0) AS order_count,
    COALESCE(SUM(o.total_amount), 0) AS sales
FROM date_series ds
LEFT JOIN orders o ON o.order_date = ds.dt AND o.status != 'cancelled'
GROUP BY ds.dt
ORDER BY ds.dt;