Recursive CTE for Hierarchies

WITH RECURSIVE org_hierarchy AS (
    -- Base case: top-level employees (no manager)
    SELECT
        employee_id,
        first_name || ' ' || last_name AS employee_name,
        manager_id,
        department_id,
        0 AS level,
        ARRAY[employee_id] AS path,
        first_name || ' ' || last_name AS hierarchy_path
    FROM employees
    WHERE manager_id IS NULL

    UNION ALL

    -- Recursive case: employees reporting to someone in the CTE
    SELECT
        e.employee_id,
        e.first_name || ' ' || last_name AS employee_name,
        e.manager_id,
        e.department_id,
        oh.level + 1 AS level,
        oh.path || e.employee_id AS path,
        oh.hierarchy_path || ' > ' || e.first_name || ' ' || e.last_name AS hierarchy_path
    FROM employees e
    INNER JOIN org_hierarchy oh ON e.manager_id = oh.employee_id
    WHERE e.employee_id != ALL(oh.path)  -- Prevent cycles
)
SELECT
    REPEAT('  ', level) || employee_name AS indented_name,
    level,
    department_id,
    hierarchy_path
FROM org_hierarchy
ORDER BY path;

-- Find all sub-categories of a given category
WITH RECURSIVE sub_categories AS (
    SELECT category_id, category_name, parent_id
    FROM categories
    WHERE category_id = :root_category_id

    UNION ALL

    SELECT c.category_id, c.category_name, c.parent_id
    FROM categories c
    JOIN sub_categories sc ON c.parent_id = sc.category_id
)
SELECT * FROM sub_categories;