CTEs — WITH Clause

-- Basic CTE
WITH top_students AS (
    SELECT name, marks, dept_id
    FROM students
    WHERE marks >= 80
)
SELECT t.name, t.marks, d.name AS dept
FROM top_students t
INNER JOIN departments d ON t.dept_id = d.id;

-- Multiple CTEs
WITH
avg_marks AS (
    SELECT dept_id, AVG(marks) AS dept_avg
    FROM students
    GROUP BY dept_id
),
top_students AS (
    SELECT s.name, s.marks, s.dept_id
    FROM students s
    INNER JOIN avg_marks a ON s.dept_id = a.dept_id
    WHERE s.marks > a.dept_avg
)
SELECT t.name, t.marks, d.name AS dept
FROM top_students t
INNER JOIN departments d ON t.dept_id = d.id;

-- Recursive CTE - employee hierarchy
WITH RECURSIVE org_chart AS (
    SELECT id, name, manager_id, 0 AS level
    FROM employees WHERE manager_id IS NULL
    UNION ALL
    SELECT e.id, e.name, e.manager_id, oc.level + 1
    FROM employees e
    INNER JOIN org_chart oc ON e.manager_id = oc.id
)
SELECT REPEAT("  ", level), name FROM org_chart;