#Step 09 — CTE와 재귀 쿼리

WITH customer_stats AS (
    SELECT customer_id,
           COUNT(*)          AS order_cnt,
           SUM(total_amount) AS sum_amount
    FROM orders
    WHERE status <> 'CANCELLED'
    GROUP BY customer_id
)
SELECT c.customer_id, c.name, s.order_cnt, s.sum_amount
FROM customer_stats s
JOIN customers c ON c.customer_id = s.customer_id
WHERE s.sum_amount >= 40000000
ORDER BY s.sum_amount DESC;

WITH monthly AS (
    SELECT DATE_FORMAT(order_date, '%Y-%m') AS ym,
           SUM(total_amount) AS amt
    FROM orders
    WHERE status <> 'CANCELLED'
    GROUP BY ym
)
SELECT cur.ym,
       cur.amt,
       prev.amt AS prev_amt,
       ROUND((cur.amt - prev.amt) / prev.amt * 100, 1) AS growth_pct
FROM monthly cur
JOIN monthly prev                       -- ← 같은 CTE 를 두 번 참조
  ON prev.ym = DATE_FORMAT(DATE_SUB(STR_TO_DATE(CONCAT(cur.ym,'-01'), '%Y-%m-%d'),
                                    INTERVAL 1 MONTH), '%Y-%m')
ORDER BY cur.ym;

EXPLAIN FORMAT=TREE
WITH monthly AS (
    SELECT DATE_FORMAT(order_date, '%Y-%m') AS ym, SUM(total_amount) AS amt
    FROM orders GROUP BY ym
)
SELECT a.ym, a.amt, b.amt FROM monthly a JOIN monthly b ON b.ym = a.ym;

WITH
paid_orders AS (                       -- 1단계: 유효 주문만 추린다
    SELECT order_id, customer_id, total_amount, order_date
    FROM orders
    WHERE status IN ('PAID','SHIPPED','DELIVERED')
),
per_customer AS (                      -- 2단계: 고객별로 집계
    SELECT customer_id,
           COUNT(*)                 AS cnt,
           ROUND(AVG(total_amount)) AS avg_amt
    FROM paid_orders
    GROUP BY customer_id
),
ranked AS (                            -- 3단계: 고객 정보를 붙인다
    SELECT p.*, c.name, c.grade
    FROM per_customer p
    JOIN customers c ON c.customer_id = p.customer_id
)
SELECT customer_id, name, grade, cnt, avg_amt
FROM ranked
ORDER BY avg_amt DESC
LIMIT 5;

WITH RECURSIVE seq AS (
    SELECT 1 AS n                       -- 앵커
    UNION ALL
    SELECT n + 1 FROM seq WHERE n < 5   -- 재귀 (n=5 가 되면 새 행이 안 나옴 → 종료)
)
SELECT n FROM seq;

WITH RECURSIVE boom AS (SELECT 1 AS n UNION ALL SELECT n+1 FROM boom)
SELECT * FROM boom;
-- ERROR 3636 (HY000): Recursive query aborted after 1001 iterations.
-- Try increasing @@cte_max_recursion_depth to a larger value.

SELECT @@cte_max_recursion_depth AS default_depth;

SET SESSION cte_max_recursion_depth = 10000;
WITH RECURSIVE seq AS (
    SELECT 1 AS n
    UNION ALL
    SELECT n + 1 FROM seq WHERE n < 5000
)
SELECT COUNT(*) AS cnt, MAX(n) AS max_n FROM seq;
SET SESSION cte_max_recursion_depth = 1000;   -- 반드시 되돌린다

WITH RECURSIVE org AS (
    -- 앵커: 최상위(상사가 없는 사람)
    SELECT employee_id, name, manager_id, dept, position, 1 AS lvl,
           CAST(name AS CHAR(200)) AS path
    FROM employees
    WHERE manager_id IS NULL
    UNION ALL
    -- 재귀: 이미 찾은 사람(o)을 상사로 두는 사람(e)을 붙인다
    SELECT e.employee_id, e.name, e.manager_id, e.dept, e.position, o.lvl + 1,
           CONCAT(o.path, ' > ', e.name)
    FROM employees e
    JOIN org o ON e.manager_id = o.employee_id
)
SELECT lvl,
       CONCAT(REPEAT('    ', lvl - 1), name) AS tree,
       position, dept, path
FROM org
ORDER BY path;

WITH RECURSIVE up AS (
    SELECT employee_id, name, manager_id, position, 0 AS up_lvl
    FROM employees
    WHERE employee_id = 13                       -- 남뷰어(주니어)
    UNION ALL
    SELECT e.employee_id, e.name, e.manager_id, e.position, u.up_lvl + 1
    FROM employees e
    JOIN up u ON e.employee_id = u.manager_id    -- ← 방향이 반대
)
SELECT up_lvl, employee_id, name, position FROM up ORDER BY up_lvl;

WITH RECURSIVE sub AS (
    SELECT employee_id AS root_id, employee_id, name
    FROM employees                                  -- 앵커: 모든 사원이 각자 루트
    UNION ALL
    SELECT s.root_id, e.employee_id, e.name
    FROM employees e
    JOIN sub s ON e.manager_id = s.employee_id
)
SELECT r.employee_id, r.name, r.position,
       COUNT(*) - 1 AS descendants                  -- 자기 자신 제외
FROM sub s
JOIN employees r ON r.employee_id = s.root_id
GROUP BY r.employee_id, r.name, r.position
ORDER BY descendants DESC, r.employee_id
LIMIT 6;

WITH RECURSIVE dates AS (
    SELECT DATE('2025-01-01') AS d
    UNION ALL
    SELECT d + INTERVAL 1 DAY FROM dates WHERE d < '2025-01-10'
)
SELECT d, DAYNAME(d) AS dow FROM dates;

SELECT DATE(order_date) AS d, COUNT(*) AS cnt
FROM orders
WHERE order_date >= '2025-03-01' AND order_date < '2025-03-11'
GROUP BY DATE(order_date)
ORDER BY d;

WITH RECURSIVE dates AS (                 -- ① 날짜 뼈대를 만든다
    SELECT DATE('2025-03-01') AS d
    UNION ALL
    SELECT d + INTERVAL 1 DAY FROM dates WHERE d < '2025-03-10'
),
daily AS (                                -- ② 실제 집계
    SELECT DATE(order_date) AS d, COUNT(*) AS cnt, SUM(total_amount) AS amt
    FROM orders
    WHERE order_date >= '2025-03-01' AND order_date < '2025-03-11'
    GROUP BY DATE(order_date)
)
SELECT dt.d,                              -- ③ 뼈대에 살을 붙인다
       COALESCE(dl.cnt, 0) AS order_cnt,
       COALESCE(dl.amt, 0) AS amount
FROM dates dt
LEFT JOIN daily dl ON dl.d = dt.d
ORDER BY dt.d;

WITH RECURSIVE months AS (
    SELECT DATE('2025-01-01') AS m
    UNION ALL
    SELECT m + INTERVAL 1 MONTH FROM months WHERE m < '2025-06-01'
)
SELECT DATE_FORMAT(mo.m, '%Y-%m')     AS ym,
       COUNT(o.order_id)              AS order_cnt,
       COALESCE(SUM(o.total_amount),0) AS amount
FROM months mo
LEFT JOIN orders o
       ON o.order_date >= mo.m
      AND o.order_date <  mo.m + INTERVAL 1 MONTH
      AND o.status <> 'CANCELLED'
GROUP BY ym
ORDER BY ym;

WITH RECURSIVE seq AS (
    SELECT 1 AS n
    UNION            -- UNION ALL 이었다면 무한루프
    SELECT 1 FROM seq
)
SELECT * FROM seq;

WITH RECURSIVE org AS (
    SELECT employee_id, name, manager_id, 1 AS lvl
    FROM employees WHERE manager_id IS NULL
    UNION ALL
    SELECT e.employee_id, e.name, e.manager_id, o.lvl + 1
    FROM employees e
    JOIN org o ON e.manager_id = o.employee_id
    WHERE o.lvl < 10          -- ← 안전장치
)
SELECT lvl, COUNT(*) AS cnt FROM org GROUP BY lvl ORDER BY lvl;

-- =====================================================================
-- Step 09 — CTE와 재귀 쿼리 : practice.sql
-- 실행: mysql -h127.0.0.1 -P3307 -ulearner -plearn1234 shop < practice.sql
-- * 이 파일은 SELECT 만 합니다. 원본 데이터를 변경하지 않습니다.
-- =====================================================================
USE shop;

-- [9-1] 비재귀 CTE : 파생 테이블을 이름 붙여 앞으로 빼낸다
WITH customer_stats AS (
    SELECT customer_id,
           COUNT(*)          AS order_cnt,
           SUM(total_amount) AS sum_amount
    FROM orders
    WHERE status <> 'CANCELLED'
    GROUP BY customer_id
)
SELECT c.customer_id, c.name, s.order_cnt, s.sum_amount
FROM customer_stats s
JOIN customers c ON c.customer_id = s.customer_id
WHERE s.sum_amount >= 40000000
ORDER BY s.sum_amount DESC;

-- [9-2] 같은 CTE 를 두 번 참조할 수 있다 (파생 테이블은 불가능한 일)
WITH monthly AS (
    SELECT DATE_FORMAT(order_date, '%Y-%m') AS ym,
           SUM(total_amount) AS amt
    FROM orders
    WHERE status <> 'CANCELLED'
    GROUP BY ym
)
SELECT cur.ym,
       cur.amt,
       prev.amt AS prev_amt,
       ROUND((cur.amt - prev.amt) / prev.amt * 100, 1) AS growth_pct
FROM monthly cur
JOIN monthly prev
  ON prev.ym = DATE_FORMAT(DATE_SUB(STR_TO_DATE(CONCAT(cur.ym,'-01'), '%Y-%m-%d'),
                                    INTERVAL 1 MONTH), '%Y-%m')
ORDER BY cur.ym;

-- [9-3] CTE 체이닝 : 앞의 CTE 를 뒤의 CTE 가 참조한다
WITH
paid_orders AS (
    SELECT order_id, customer_id, total_amount, order_date
    FROM orders
    WHERE status IN ('PAID','SHIPPED','DELIVERED')
),
per_customer AS (
    SELECT customer_id,
           COUNT(*)                 AS cnt,
           ROUND(AVG(total_amount)) AS avg_amt
    FROM paid_orders
    GROUP BY customer_id
),
ranked AS (
    SELECT p.*, c.name, c.grade
    FROM per_customer p
    JOIN customers c ON c.customer_id = p.customer_id
)
SELECT customer_id, name, grade, cnt, avg_amt
FROM ranked
ORDER BY avg_amt DESC
LIMIT 5;

-- [9-4] 가장 단순한 재귀 CTE : 1부터 5까지
WITH RECURSIVE seq AS (
    SELECT 1 AS n                       -- 앵커(anchor) : 시작점
    UNION ALL
    SELECT n + 1 FROM seq WHERE n < 5   -- 재귀 : 자기 자신을 참조
)
SELECT n FROM seq;

-- [9-5] 종료 조건을 빼면? → cte_max_recursion_depth 가 우리를 구해준다
-- WITH RECURSIVE boom AS (SELECT 1 AS n UNION ALL SELECT n+1 FROM boom)
-- SELECT * FROM boom;
-- ERROR 3636 (HY000): Recursive query aborted after 1001 iterations.
-- Try increasing @@cte_max_recursion_depth to a larger value.

-- [9-6] 기본 재귀 깊이 한계 확인
SELECT @@cte_max_recursion_depth AS default_depth;

-- [9-7] 깊이를 늘려서 1~5000 만들기 (세션 한정)
SET SESSION cte_max_recursion_depth = 10000;
WITH RECURSIVE seq AS (
    SELECT 1 AS n
    UNION ALL
    SELECT n + 1 FROM seq WHERE n < 5000
)
SELECT COUNT(*) AS cnt, MAX(n) AS max_n FROM seq;
SET SESSION cte_max_recursion_depth = 1000;

-- [9-8] 재귀 CTE 로 조직도 전개 (하향 : CEO → 말단)
WITH RECURSIVE org AS (
    SELECT employee_id, name, manager_id, dept, position, 1 AS lvl,
           CAST(name AS CHAR(200)) AS path
    FROM employees
    WHERE manager_id IS NULL              -- 앵커: 최상위
    UNION ALL
    SELECT e.employee_id, e.name, e.manager_id, e.dept, e.position, o.lvl + 1,
           CONCAT(o.path, ' > ', e.name)
    FROM employees e
    JOIN org o ON e.manager_id = o.employee_id   -- 부모를 찾아 붙인다
)
SELECT lvl,
       CONCAT(REPEAT('    ', lvl - 1), name) AS tree,
       position, dept, path
FROM org
ORDER BY path;

-- [9-9] 특정 사원의 상위 라인 거슬러 올라가기 (상향)
WITH RECURSIVE up AS (
    SELECT employee_id, name, manager_id, position, 0 AS up_lvl
    FROM employees
    WHERE employee_id = 13                -- 남뷰어(주니어)
    UNION ALL
    SELECT e.employee_id, e.name, e.manager_id, e.position, u.up_lvl + 1
    FROM employees e
    JOIN up u ON e.employee_id = u.manager_id
)
SELECT up_lvl, employee_id, name, position FROM up ORDER BY up_lvl;

-- [9-10] 부하 직원 수 세기 (하위 조직 전체 인원)
WITH RECURSIVE sub AS (
    SELECT employee_id AS root_id, employee_id, name
    FROM employees
    UNION ALL
    SELECT s.root_id, e.employee_id, e.name
    FROM employees e
    JOIN sub s ON e.manager_id = s.employee_id
)
SELECT r.employee_id, r.name, r.position,
       COUNT(*) - 1 AS descendants     -- 자기 자신 제외
FROM sub s
JOIN employees r ON r.employee_id = s.root_id
GROUP BY r.employee_id, r.name, r.position
ORDER BY descendants DESC, r.employee_id
LIMIT 6;

-- [9-11] 재귀 CTE 로 날짜 시퀀스 생성 (2025-01-01 ~ 2025-01-10)
WITH RECURSIVE dates AS (
    SELECT DATE('2025-01-01') AS d
    UNION ALL
    SELECT d + INTERVAL 1 DAY FROM dates WHERE d < '2025-01-10'
)
SELECT d, DAYNAME(d) AS dow FROM dates;

-- [9-12] 빈 구간 채우기(gap filling) : 주문이 없는 날도 0으로 표시
WITH RECURSIVE dates AS (
    SELECT DATE('2025-03-01') AS d
    UNION ALL
    SELECT d + INTERVAL 1 DAY FROM dates WHERE d < '2025-03-10'
),
daily AS (
    SELECT DATE(order_date) AS d, COUNT(*) AS cnt, SUM(total_amount) AS amt
    FROM orders
    WHERE order_date >= '2025-03-01' AND order_date < '2025-03-11'
    GROUP BY DATE(order_date)
)
SELECT dt.d,
       COALESCE(dl.cnt, 0) AS order_cnt,
       COALESCE(dl.amt, 0) AS amount
FROM dates dt
LEFT JOIN daily dl ON dl.d = dt.d
ORDER BY dt.d;

-- [9-13] 빈 구간을 채우지 않으면? (비교용 — 없는 날은 아예 사라진다)
SELECT DATE(order_date) AS d, COUNT(*) AS cnt
FROM orders
WHERE order_date >= '2025-03-01' AND order_date < '2025-03-11'
GROUP BY DATE(order_date)
ORDER BY d;

-- [9-14] 월 시퀀스 생성 + 월별 매출 (빈 달 0)
WITH RECURSIVE months AS (
    SELECT DATE('2025-01-01') AS m
    UNION ALL
    SELECT m + INTERVAL 1 MONTH FROM months WHERE m < '2025-06-01'
)
SELECT DATE_FORMAT(mo.m, '%Y-%m') AS ym,
       COUNT(o.order_id)              AS order_cnt,
       COALESCE(SUM(o.total_amount),0) AS amount
FROM months mo
LEFT JOIN orders o
       ON o.order_date >= mo.m
      AND o.order_date <  mo.m + INTERVAL 1 MONTH
      AND o.status <> 'CANCELLED'
GROUP BY ym
ORDER BY ym;

-- [9-15] tally 테이블로도 같은 일을 할 수 있다 (재귀보다 빠르다)
SELECT DATE_ADD('2025-01-01', INTERVAL t.n - 1 DAY) AS d
FROM tally t
WHERE t.n <= 5;

-- [9-16] 카테고리 계층 전개 (재귀 CTE)
WITH RECURSIVE cat_tree AS (
    SELECT category_id, parent_id, name, 1 AS lvl,
           CAST(name AS CHAR(200)) AS full_path
    FROM categories
    WHERE parent_id IS NULL
    UNION ALL
    SELECT c.category_id, c.parent_id, c.name, t.lvl + 1,
           CONCAT(t.full_path, ' / ', c.name)
    FROM categories c
    JOIN cat_tree t ON c.parent_id = t.category_id
)
SELECT category_id, lvl, full_path FROM cat_tree ORDER BY full_path LIMIT 8;

-- [9-17] 무한루프 방지 : 재귀 CTE 안에서는 UNION ALL 대신 UNION 을 쓰면 중복이 제거된다
--        (사이클이 있는 그래프에서 유용하지만, 완전한 방어는 아니다)
WITH RECURSIVE seq AS (
    SELECT 1 AS n
    UNION            -- DISTINCT: 같은 행이 다시 나오면 재귀가 멈춘다
    SELECT 1 FROM seq
)
SELECT * FROM seq;

-- [9-18] 방어적 패턴 : 깊이 상한을 쿼리 안에 직접 넣는다
WITH RECURSIVE org AS (
    SELECT employee_id, name, manager_id, 1 AS lvl
    FROM employees WHERE manager_id IS NULL
    UNION ALL
    SELECT e.employee_id, e.name, e.manager_id, o.lvl + 1
    FROM employees e
    JOIN org o ON e.manager_id = o.employee_id
    WHERE o.lvl < 10          -- ← 안전장치. 데이터에 사이클이 있어도 10에서 멈춘다
)
SELECT lvl, COUNT(*) AS cnt FROM org GROUP BY lvl ORDER BY lvl;

-- [9-19] CTE 는 INSERT/UPDATE/DELETE 앞에도 붙일 수 있다 (구문만 확인)
-- WITH target AS (SELECT order_id FROM orders WHERE status = 'PENDING')
-- DELETE FROM orders WHERE order_id IN (SELECT order_id FROM target);
--   → Step 11 에서 실제로 실습합니다(사본 테이블에서).

-- [9-20] 실행계획 : 여러 번 참조되는 CTE 는 한 번만 구체화(materialize)된다
EXPLAIN FORMAT=TREE
WITH monthly AS (
    SELECT DATE_FORMAT(order_date, '%Y-%m') AS ym, SUM(total_amount) AS amt
    FROM orders GROUP BY ym
)
SELECT a.ym, a.amt, b.amt FROM monthly a JOIN monthly b ON b.ym = a.ym;

-- =====================================================================
-- Step 09 — CTE와 재귀 쿼리 : 연습문제
-- 실행: mysql -h127.0.0.1 -P3307 -ulearner -plearn1234 shop < exercise.sql
-- 정답은 solution.sql
-- =====================================================================
USE shop;

-- ---------------------------------------------------------------------
-- Q1. CTE 를 사용해 "카테고리별 상품 수"를 구하고,
--     상품이 4개 이상인 카테고리만 출력하시오.
--     컬럼: category_id, cat_name, product_cnt / 정렬: product_cnt DESC
-- ---------------------------------------------------------------------


-- ---------------------------------------------------------------------
-- Q2. 재귀 CTE 로 1~20 중 짝수만 출력하시오. (컬럼: n)
-- ---------------------------------------------------------------------


-- ---------------------------------------------------------------------
-- Q3. 재귀 CTE 로 employees 조직도를 전개하고,
--     각 사원의 depth(최상위=1)와 들여쓰기된 이름을 출력하시오.
--     컬럼: employee_id, depth, tree(들여쓰기+이름), position
--     정렬: 트리 순서(경로 문자열 기준)
-- ---------------------------------------------------------------------


-- ---------------------------------------------------------------------
-- Q4. employee_id = 17 (노총무) 의 모든 상위 관리자를 가까운 순서로 출력하시오.
--     컬럼: step(0=본인), employee_id, name, position
-- ---------------------------------------------------------------------


-- ---------------------------------------------------------------------
-- Q5. 2025-06-01 ~ 2025-06-15 의 일별 주문 건수를 출력하시오.
--     주문이 없는 날도 0 으로 반드시 나와야 한다. (15행)
--     컬럼: d, order_cnt
-- ---------------------------------------------------------------------


-- ---------------------------------------------------------------------
-- Q6. CTE 체이닝(2단 이상)으로
--     VIP/GOLD 등급 고객의 월별 매출(취소 제외)을 구하시오.
--     컬럼: ym, order_cnt, amount / 정렬: ym / 상위 6행만
-- ---------------------------------------------------------------------


-- ---------------------------------------------------------------------
-- Q7. 재귀 CTE 로 categories 계층을 전개하고
--     '대분류 / 소분류' 형태의 경로 문자열을 출력하시오.
--     컬럼: category_id, lvl, full_path / 정렬: full_path
-- ---------------------------------------------------------------------


-- ---------------------------------------------------------------------
-- Q8. cte_max_recursion_depth 를 세션 한정으로 조정해
--     1 ~ 3000 의 합을 재귀 CTE 로 구하시오. (검산: 4,501,500)
--     끝난 뒤 기본값 1000 으로 되돌릴 것.
-- ---------------------------------------------------------------------

-- =====================================================================
-- Step 09 — CTE와 재귀 쿼리 : 정답 + 해설
-- =====================================================================
USE shop;

-- ---------------------------------------------------------------------
-- A1. 비재귀 CTE.
--     집계를 CTE 로 빼면 바깥 쿼리가 "무엇을 하려는지"만 남아 읽기 쉽다.
--     HAVING 으로도 되지만, 이후 단계가 붙기 시작하면 CTE 쪽이 확장성이 좋다.
-- ---------------------------------------------------------------------
WITH cat_cnt AS (
    SELECT category_id, COUNT(*) AS product_cnt
    FROM products
    GROUP BY category_id
)
SELECT cc.category_id, c.name AS cat_name, cc.product_cnt
FROM cat_cnt cc
JOIN categories c ON c.category_id = cc.category_id
WHERE cc.product_cnt >= 4
ORDER BY cc.product_cnt DESC, cc.category_id;
-- → 주변기기(5), 남성의류(4), 여성의류(4), 노트북(4), 신선식품(4)

-- ---------------------------------------------------------------------
-- A2. 재귀로 1..20 을 만든 뒤 바깥에서 짝수만 거른다.
--     재귀 부분에서 n+2 로 건너뛰어도 되지만(더 효율적),
--     "생성 → 필터"의 분리가 읽기엔 더 명확하다.
-- ---------------------------------------------------------------------
WITH RECURSIVE seq AS (
    SELECT 1 AS n
    UNION ALL
    SELECT n + 1 FROM seq WHERE n < 20
)
SELECT n FROM seq WHERE n % 2 = 0;
-- → 2,4,6,...,20 (10행)

-- 대안: 애초에 짝수만 생성 (반복 횟수가 절반)
WITH RECURSIVE evens AS (
    SELECT 2 AS n
    UNION ALL
    SELECT n + 2 FROM evens WHERE n < 20
)
SELECT n FROM evens;

-- ---------------------------------------------------------------------
-- A3. 조직도 전개.
--     path 를 만들어 두면 ORDER BY path 만으로 트리(깊이 우선) 순서가 나온다.
--     앵커에서 CAST(name AS CHAR(200)) 을 하지 않으면
--     path 가 VARCHAR(50) 로 고정되어 긴 경로가 잘린다. (매우 흔한 실수)
-- ---------------------------------------------------------------------
WITH RECURSIVE org AS (
    SELECT employee_id, name, manager_id, position, 1 AS depth,
           CAST(name AS CHAR(200)) AS path
    FROM employees
    WHERE manager_id IS NULL
    UNION ALL
    SELECT e.employee_id, e.name, e.manager_id, e.position, o.depth + 1,
           CONCAT(o.path, ' > ', e.name)
    FROM employees e
    JOIN org o ON e.manager_id = o.employee_id
)
SELECT employee_id, depth,
       CONCAT(REPEAT('    ', depth - 1), name) AS tree,
       position
FROM org
ORDER BY path;
-- → 18행. depth 1(1명) / 2(3명) / 3(4명) / 4(10명)

-- ---------------------------------------------------------------------
-- A4. 상향 재귀. 조인 방향이 A3 과 반대다.
--     A3:  e.manager_id = o.employee_id   (아래로)
--     A4:  e.employee_id = u.manager_id   (위로)
-- ---------------------------------------------------------------------
WITH RECURSIVE up AS (
    SELECT employee_id, name, manager_id, position, 0 AS step
    FROM employees
    WHERE employee_id = 17
    UNION ALL
    SELECT e.employee_id, e.name, e.manager_id, e.position, u.step + 1
    FROM employees e
    JOIN up u ON e.employee_id = u.manager_id
)
SELECT step, employee_id, name, position FROM up ORDER BY step;
-- → 노총무(0) > 윤사람(1) > 오지원(2) > 정한별(3)

-- ---------------------------------------------------------------------
-- A5. gap filling.
--     핵심 3가지:
--       ① 날짜 뼈대를 재귀 CTE 로 먼저 만든다
--       ② 뼈대가 LEFT JOIN 의 "왼쪽"에 와야 없는 날이 살아남는다
--       ③ COUNT(o.order_id) 를 써야 없는 날이 0 이 된다.
--          COUNT(*) 로 쓰면 LEFT JOIN 으로 붙은 NULL 행도 1로 세어 1이 나온다!
-- ---------------------------------------------------------------------
WITH RECURSIVE dates AS (
    SELECT DATE('2025-06-01') AS d
    UNION ALL
    SELECT d + INTERVAL 1 DAY FROM dates WHERE d < '2025-06-15'
)
SELECT dt.d, COUNT(o.order_id) AS order_cnt
FROM dates dt
LEFT JOIN orders o ON DATE(o.order_date) = dt.d
GROUP BY dt.d
ORDER BY dt.d;
-- → 15행. 주문 없는 날은 0.
--   (주의: 위는 DATE(o.order_date) 에 함수를 씌워 인덱스를 못 탄다.
--    행이 적은 학습용이라 괜찮지만, 실무에선 아래처럼 집계를 먼저 하고 조인하라.)

WITH RECURSIVE dates AS (
    SELECT DATE('2025-06-01') AS d
    UNION ALL
    SELECT d + INTERVAL 1 DAY FROM dates WHERE d < '2025-06-15'
),
daily AS (
    SELECT DATE(order_date) AS d, COUNT(*) AS cnt
    FROM orders
    WHERE order_date >= '2025-06-01' AND order_date < '2025-06-16'  -- 범위조건 → 인덱스 가능
    GROUP BY DATE(order_date)
)
SELECT dt.d, COALESCE(dl.cnt, 0) AS order_cnt
FROM dates dt
LEFT JOIN daily dl ON dl.d = dt.d
ORDER BY dt.d;

-- ---------------------------------------------------------------------
-- A6. CTE 체이닝. 단계마다 이름이 붙어 있어 중간 결과를 따로 실행해보기 쉽다.
-- ---------------------------------------------------------------------
WITH
vip_customers AS (
    SELECT customer_id FROM customers WHERE grade IN ('VIP','GOLD')
),
vip_orders AS (
    SELECT o.order_id, o.order_date, o.total_amount
    FROM orders o
    JOIN vip_customers v ON v.customer_id = o.customer_id
    WHERE o.status <> 'CANCELLED'
)
SELECT DATE_FORMAT(order_date, '%Y-%m') AS ym,
       COUNT(*)                          AS order_cnt,
       SUM(total_amount)                 AS amount
FROM vip_orders
GROUP BY ym
ORDER BY ym
LIMIT 6;

-- ---------------------------------------------------------------------
-- A7. 카테고리 계층 전개. employees 와 완전히 같은 패턴이다.
--     계층형 데이터는 전부 이 틀로 푼다.
-- ---------------------------------------------------------------------
WITH RECURSIVE cat_tree AS (
    SELECT category_id, parent_id, name, 1 AS lvl,
           CAST(name AS CHAR(200)) AS full_path
    FROM categories
    WHERE parent_id IS NULL
    UNION ALL
    SELECT c.category_id, c.parent_id, c.name, t.lvl + 1,
           CONCAT(t.full_path, ' / ', c.name)
    FROM categories c
    JOIN cat_tree t ON c.parent_id = t.category_id
)
SELECT category_id, lvl, full_path FROM cat_tree ORDER BY full_path;
-- → 17행 (대분류 5 + 소분류 12)

-- ---------------------------------------------------------------------
-- A8. 깊이 한계 조정.
--     기본 1000 이므로 3000 까지 가려면 반드시 늘려야 한다.
--     GLOBAL 이 아니라 SESSION 으로 바꾸고, 끝나면 되돌리는 것이 예의다.
-- ---------------------------------------------------------------------
SET SESSION cte_max_recursion_depth = 5000;

WITH RECURSIVE seq AS (
    SELECT 1 AS n
    UNION ALL
    SELECT n + 1 FROM seq WHERE n < 3000
)
SELECT COUNT(*) AS cnt, SUM(n) AS total FROM seq;
-- → cnt=3000, total=4501500

SET SESSION cte_max_recursion_depth = 1000;

-- 참고: tally 테이블을 쓰면 재귀도, 설정 변경도 필요 없다.
SELECT COUNT(*) AS cnt, SUM(n) AS total FROM tally WHERE n <= 3000;