Recursive CTE for hierarchical data in PostgreSQL - Mini Project: Build & Apply

Building a Hierarchical Employee Directory with Recursive CTE

📖 Scenario: You are creating an employee directory for a company. Each employee may have a manager, and managers can have their own managers, forming a hierarchy. You want to find the full chain of command for each employee.

🎯 Goal: Build a recursive SQL query using a Common Table Expression (CTE) to list each employee along with their full management chain.

📋 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 employee_hierarchy to find each employee's management chain.

Select employee names along with their managers' names in hierarchical order.

💡 Why This Matters

🌍 Real World

Companies often store employee-manager relationships in databases. Recursive CTEs help retrieve full organizational charts or reporting lines.

💼 Career

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

1

Create the employees table and insert data

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

PostgreSQL

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

Need a hint?

Use CREATE TABLE with the specified columns and insert the exact rows with INSERT INTO.

2

Define the base query for the recursive CTE

Start a recursive CTE named employee_hierarchy. Define the base query to select id, name, and manager_id from employees where manager_id IS NULL.

PostgreSQL

Need a hint?

Start the recursive CTE with the base case selecting employees without managers.

3

Add the recursive part to the CTE

Add the recursive query to employee_hierarchy that selects employees joined with employee_hierarchy on employees.manager_id = employee_hierarchy.id. Select employees.id, employees.name, and employees.manager_id. Combine with the base query using UNION ALL.

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', 2),
(4, 'Diana', 2),
(5, 'Eve', 1);

WITH RECURSIVE employee_hierarchy AS (
    SELECT id, name, manager_id
    FROM employees
    WHERE manager_id IS NULL
    UNION ALL
    SELECT employees.id, employees.name, employees.manager_id
    FROM employees
    JOIN employee_hierarchy ON employees.manager_id = employee_hierarchy.id
    -- Your code here
)

Need a hint?

Add the recursive part that joins employees to their managers in the hierarchy.

4

Select from the recursive CTE to show the hierarchy

Write a final SELECT statement after the CTE to select id, name, and manager_id from employee_hierarchy ordered by id.

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', 2),
(4, 'Diana', 2),
(5, 'Eve', 1);

WITH RECURSIVE employee_hierarchy AS (
    SELECT id, name, manager_id
    FROM employees
    WHERE manager_id IS NULL
    UNION ALL
    SELECT employees.id, employees.name, employees.manager_id
    FROM employees
    JOIN employee_hierarchy ON employees.manager_id = employee_hierarchy.id
)

-- Your code here

Need a hint?

Finish by selecting from the recursive CTE and ordering the results.