서브쿼리와 CTE — 복잡한 조건 처리하기

-- 평균 급여보다 많이 받는 직원
SELECT name, salary
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);

-- 서브쿼리는 괄호 안에
-- 단일 값 반환 (스칼라 서브쿼리)
SELECT
    name,
    salary,
    salary - (SELECT AVG(salary) FROM employees) AS diff_from_avg
FROM employees;

-- 주문이 있는 고객만 조회
SELECT name FROM customers
WHERE id IN (
    SELECT DISTINCT customer_id FROM orders
);

-- 주문이 없는 고객
SELECT name FROM customers
WHERE id NOT IN (
    SELECT DISTINCT customer_id FROM orders WHERE customer_id IS NOT NULL
);

-- 주문이 있는 고객
SELECT c.name
FROM customers c
WHERE EXISTS (
    SELECT 1 FROM orders o WHERE o.customer_id = c.id
);

-- 50000원 이상 주문한 고객
SELECT c.name
FROM customers c
WHERE EXISTS (
    SELECT 1 FROM orders o
    WHERE o.customer_id = c.id
    AND o.amount >= 50000
);

-- 부서별 평균 급여를 임시 테이블처럼 사용
SELECT
    e.name,
    e.salary,
    dept_avg.avg_salary
FROM employees e
JOIN (
    SELECT department, AVG(salary) AS avg_salary
    FROM employees
    GROUP BY department
) AS dept_avg ON e.department = dept_avg.department
WHERE e.salary > dept_avg.avg_salary;

-- 위 쿼리를 CTE로 개선
WITH dept_averages AS (
    SELECT department, AVG(salary) AS avg_salary
    FROM employees
    GROUP BY department
),
above_average AS (
    SELECT e.name, e.department, e.salary, d.avg_salary
    FROM employees e
    JOIN dept_averages d ON e.department = d.department
    WHERE e.salary > d.avg_salary
)
SELECT * FROM above_average
ORDER BY department, salary DESC;

-- 조직도: 모든 직속/간접 부하 찾기
WITH RECURSIVE subordinates AS (
    -- 기준점: 특정 직원
    SELECT id, name, manager_id, 0 AS depth
    FROM employees
    WHERE id = 1

    UNION ALL

    -- 재귀: 직속 부하 계속 추가
    SELECT e.id, e.name, e.manager_id, s.depth + 1
    FROM employees e
    JOIN subordinates s ON e.manager_id = s.id
)
SELECT * FROM subordinates ORDER BY depth;

-- 급여 순위 (RANK)
SELECT
    name,
    department,
    salary,
    RANK() OVER (ORDER BY salary DESC) AS overall_rank,
    RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS dept_rank
FROM employees;

-- 결과
-- name | department | salary  | overall_rank | dept_rank
-- -----|------------|---------|-------------|----------
-- 박민준 | 개발팀    | 6500000 | 1           | 1
-- 정태양 | 개발팀    | 5800000 | 2           | 2
-- ...