Postgres CTEs: how I query tree structures without touching application code

CREATE TABLE categories (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    parent_id INTEGER REFERENCES categories(id),
    slug TEXT NOT NULL
);

-- Non-recursive CTE: readable multi-step query
WITH
recent_orders AS (
    SELECT user_id, COUNT(*) AS order_count, SUM(amount_cents) AS total
    FROM orders
    WHERE created_at > NOW() - INTERVAL '90 days'
    GROUP BY user_id
),
high_value_users AS (
    SELECT user_id
    FROM recent_orders
    WHERE total > 100000  -- > $1000
),
churned_users AS (
    SELECT id AS user_id
    FROM users
    WHERE last_login < NOW() - INTERVAL '60 days'
)
SELECT u.email, ro.order_count, ro.total
FROM users u
JOIN recent_orders ro ON ro.user_id = u.id
WHERE u.id IN (SELECT user_id FROM high_value_users)
  AND u.id NOT IN (SELECT user_id FROM churned_users);

-- Get the full path from a leaf category to the root
-- E.g., Laptops -> Computers -> Electronics -> Home

WITH RECURSIVE category_path AS (
    -- Base case: start at the leaf category
    SELECT 
        id,
        name,
        parent_id,
        1 AS depth,
        ARRAY[name] AS path_names,
        ARRAY[id] AS path_ids
    FROM categories
    WHERE id = $1  -- Starting category ID
    
    UNION ALL
    
    -- Recursive case: join to parent
    SELECT
        c.id,
        c.name,
        c.parent_id,
        cp.depth + 1,
        ARRAY[c.name] || cp.path_names,
        ARRAY[c.id] || cp.path_ids
    FROM categories c
    JOIN category_path cp ON cp.parent_id = c.id
)
SELECT path_names, path_ids, depth
FROM category_path
ORDER BY depth DESC
LIMIT 1;  -- Get the full path (deepest row has the complete path)

-- Get all subcategories under "Electronics" (any depth)
WITH RECURSIVE subcategories AS (
    -- Base: the root category
    SELECT id, name, parent_id, 0 AS depth
    FROM categories
    WHERE id = $1  -- Electronics ID
    
    UNION ALL
    
    -- Recursive: all children
    SELECT c.id, c.name, c.parent_id, s.depth + 1
    FROM categories c
    JOIN subcategories s ON s.id = c.parent_id
)
SELECT id, name, depth,
       REPEAT('  ', depth) || name AS indented_name  -- Visual indentation
FROM subcategories
ORDER BY depth, name;

CREATE TABLE employees (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    manager_id INTEGER REFERENCES employees(id),
    department TEXT,
    salary INTEGER
);

-- Get all direct and indirect reports under a manager
-- Plus aggregated stats per level
WITH RECURSIVE org_chart AS (
    SELECT id, name, manager_id, department, salary, 0 AS level
    FROM employees
    WHERE id = $1  -- Manager ID
    
    UNION ALL
    
    SELECT e.id, e.name, e.manager_id, e.department, e.salary, oc.level + 1
    FROM employees e
    JOIN org_chart oc ON oc.id = e.manager_id
    WHERE oc.level < 10  -- Safety: prevent infinite loops on bad data
)
SELECT
    level,
    COUNT(*) AS headcount,
    ROUND(AVG(salary)) AS avg_salary,
    SUM(salary) AS total_salary_cost
FROM org_chart
WHERE level > 0  -- Exclude the manager themselves
GROUP BY level
ORDER BY level;

CREATE TABLE connections (
    from_user INTEGER,
    to_user INTEGER,
    PRIMARY KEY (from_user, to_user)
);

-- Find shortest connection path between two users (like LinkedIn degrees)
WITH RECURSIVE paths AS (
    SELECT 
        from_user AS start_user,
        to_user AS current_user,
        ARRAY[from_user, to_user] AS path,
        1 AS hops
    FROM connections
    WHERE from_user = $1  -- Starting user
    
    UNION ALL
    
    SELECT
        p.start_user,
        c.to_user,
        p.path || c.to_user,
        p.hops + 1
    FROM connections c
    JOIN paths p ON p.current_user = c.from_user
    WHERE c.to_user != ALL(p.path)  -- Avoid cycles
      AND p.hops < 6               -- Max 6 degrees of separation
)
SELECT path, hops
FROM paths
WHERE current_user = $2  -- Target user
ORDER BY hops
LIMIT 1;