Recursive CTE for Hierarchical Data

-- Employee org chart
WITH RECURSIVE org_tree AS (
    -- Anchor: top-level managers
    SELECT
        id, name, manager_id,
        name::text AS path,
        0 AS depth
    FROM employees
    WHERE manager_id IS NULL

    UNION ALL

    -- Recursive: find direct reports
    SELECT
        e.id, e.name, e.manager_id,
        t.path || ' > ' || e.name,
        t.depth + 1
    FROM employees e
    JOIN org_tree t ON e.manager_id = t.id
)
SELECT
    REPEAT('  ', depth) || name AS org_chart,
    path,
    depth
FROM org_tree
ORDER BY path;

-- Category breadcrumbs
WITH RECURSIVE cat_path AS (
    SELECT id, name, parent_id, ARRAY[name] AS breadcrumb
    FROM categories WHERE parent_id IS NULL
    UNION ALL
    SELECT c.id, c.name, c.parent_id, p.breadcrumb || c.name
    FROM categories c
    JOIN cat_path p ON c.parent_id = p.id
)
SELECT id, name, array_to_string(breadcrumb, ' / ') AS full_path
FROM cat_path
WHERE id = 42;

-- Generate date series (recursive)
WITH RECURSIVE dates AS (
    SELECT DATE '2024-01-01' AS d
    UNION ALL
    SELECT d + 1 FROM dates WHERE d < DATE '2024-12-31'
)
SELECT d AS date,
    EXTRACT(dow FROM d) AS day_of_week,
    TO_CHAR(d, 'Month') AS month_name
FROM dates;