Recursive CTE for graph traversal in PostgreSQL - Mini Project: Build & Apply

Recursive CTE for Graph Traversal

📖 Scenario: You are working with a company's organizational chart stored in a database. Each employee has a manager, and you want to find all employees under a specific manager, including indirect reports.

🎯 Goal: Build a recursive SQL query using a Common Table Expression (CTE) to find all employees reporting directly or indirectly to a given manager.

📋 What You'll Learn

Create a table called employees with columns id, name, and manager_id

Insert the given employee data with their managers

Write a recursive CTE named subordinates to find all employees under a specific manager

Select all employee names and their levels under the manager

💡 Why This Matters

🌍 Real World

Companies often store organizational charts in databases. Recursive queries help find all employees under a manager, useful for reporting and permissions.

💼 Career

Understanding recursive CTEs is important for database developers and analysts working with hierarchical data like org charts, file systems, or category trees.

1

Create the employees table and insert data

Create a table called employees with columns id (integer), name (text), and manager_id (integer). Then insert these exact rows: (1, 'Alice', NULL), (2, 'Bob', 1), (3, 'Charlie', 1), (4, 'David', 2), (5, 'Eve', 2).

PostgreSQL

-- Create the employees table and insert data
-- Your code here

Need a hint?

Use CREATE TABLE to define the table and INSERT INTO to add rows.

2

Set the starting manager ID

Create a variable or parameter called start_manager_id and set it to 1. This will be the manager whose subordinates we want to find.

PostgreSQL

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

INSERT INTO employees (id, name, manager_id) VALUES
(1, 'Alice', NULL),
(2, 'Bob', 1),
(3, 'Charlie', 1),
(4, 'David', 2),
(5, 'Eve', 2);

-- Set the starting manager ID
-- Your code here

Need a hint?

Use \set start_manager_id 1 in psql to define a variable.

3

Write the recursive CTE subordinates

Write a recursive CTE named subordinates that starts with employees whose manager_id equals :start_manager_id. Then recursively find employees whose manager_id is in the previous level. Include columns id, name, and level (starting at 1 for direct reports).

PostgreSQL

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

INSERT INTO employees (id, name, manager_id) VALUES
(1, 'Alice', NULL),
(2, 'Bob', 1),
(3, 'Charlie', 1),
(4, 'David', 2),
(5, 'Eve', 2);

\set start_manager_id 1

WITH RECURSIVE subordinates AS (
    -- Your code here
)

Need a hint?

Start with direct reports, then join recursively on manager_id.

4

Select all subordinates with their levels

Write a SELECT statement to get name and level from the subordinates CTE. Order the results by level ascending and then by name ascending.

PostgreSQL

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

INSERT INTO employees (id, name, manager_id) VALUES
(1, 'Alice', NULL),
(2, 'Bob', 1),
(3, 'Charlie', 1),
(4, 'David', 2),
(5, 'Eve', 2);

\set start_manager_id 1

WITH RECURSIVE subordinates AS (
    SELECT id, name, 1 AS level
    FROM employees
    WHERE manager_id = :start_manager_id
    UNION ALL
    SELECT e.id, e.name, s.level + 1
    FROM employees e
    INNER JOIN subordinates s ON e.manager_id = s.id
)

-- Your code here

Need a hint?

Select from the CTE and order by level and name.