SQL Recursive CTE for Org Hierarchy: Depth & Path

READY-TO-USE PROMPT

Copy Prompt

prompt.txt

As a database engineer specializing in SQL query optimization and hierarchical data structures.

### Context
You are tasked with modeling an organizational hierarchy from a flat employee table. The goal is to represent the 'reports to' structure as a tree, including each employee's depth within the hierarchy and their full organizational path. The solution must be portable across common SQL databases.

### Task
Develop a complete SQL solution using a Recursive Common Table Expression (CTE) to construct an organizational hierarchy. This solution must include:

1. **DDL (Data Definition Language)**: A `CREATE TABLE` statement for a sample `{{employee_table_name}}` with `employee_id`, `employee_name`, and `manager_id` columns. Ensure `manager_id` can be NULL for the top-level employee(s).
2. **Sample Data**: `INSERT` statements to populate the `{{employee_table_name}}` with at least 10 employees, demonstrating multiple levels of hierarchy and a clear root. Include a designated root employee with a NULL `manager_id` or a specific `{{root_employee_id}}`.
3. **Recursive CTE Query**: The core SQL query that:
* Starts from the specified `{{root_employee_id}}` or all employees with `NULL` `manager_id` if no specific root is provided.
* Recursively traverses the `employee_id` to `manager_id` relationship.
* For each employee, returns `employee_id`, `employee_name`, `manager_id`, `hierarchy_level` (depth), and `full_path` (e.g., "CEO > VP > Director > Employee").
4. **Expected Query Plan Notes**: Briefly describe the expected execution plan characteristics, focusing on how the recursive nature might impact performance and potential optimizations.
5. **Index Recommendation**: Suggest specific indexes that would improve the performance of this recursive CTE query.

### Constraints
* Use standard SQL syntax compatible with PostgreSQL, SQL Server, and MySQL 8.0+.
* The `full_path` should use a clear delimiter (e.g., ' > ').
* The `hierarchy_level` should start at 1 for the root(s).
* The solution must be presented as a single, executable SQL script block, with comments where appropriate.
* Prioritize clarity and maintainability alongside performance considerations.

### Output
Provide the complete SQL solution, including DDL, sample data, the recursive CTE query, expected query plan notes, and index recommendations, structured clearly.

Editor's note

Why this prompt matters

Many organizations struggle with representing their internal structure in a queryable format. Flat employee tables, while common, often lack the immediate insight into reporting lines, departmental depth, or an individual's position within the broader hierarchy. This makes tasks like generating an organizational chart, calculating management spans, or analyzing reporting structures cumbersome and error-prone.

This workflow provides a standardized approach for data engineers and analysts to transform raw employee data into a fully traversable organizational tree. It's particularly useful when you need to understand the relationships between employees, determine their hierarchical level, or trace their full reporting path from the top down. Reach for this solution when your reporting requirements extend beyond simple direct reports and demand a clear, structured view of the entire company's reporting architecture. It ensures consistency and accuracy in hierarchical data analysis.

Anatomy

Prompt engineering breakdown

Role

As a database engineer specializing in SQL query optimization and hierarchical data structures.

Context

Modeling an organizational hierarchy from a flat employee table, representing the 'reports to' structure as a tree with depth and full path, with a portable solution.

Goal

Develop a complete SQL solution using a Recursive CTE to construct an organizational hierarchy, including DDL, sample data, the recursive CTE query, expected query plan notes, and index recommendations.

Constraints

Standard SQL (PostgreSQL, SQL Server, MySQL 8.0+), ' > ' delimiter for full_path, hierarchy_level starts at 1, single executable SQL script, prioritize clarity/maintainability/performance.

Output format

Complete SQL solution: DDL, sample data, recursive CTE query, expected query plan notes, index recommendations, structured clearly as a single script block.

Why this structure works

This prompt uses role priming to establish the persona of a database engineer, setting expectations for a technical and optimized solution. Explicit constraints on SQL dialect, output format (e.g., delimiter, hierarchy level), and included components (DDL, plan notes) guide the model to produce a comprehensive and usable script. The detailed breakdown of required outputs ensures all necessary elements are present for a practical, production-ready solution.

Pick your version

Prompt variations

BeginnerWorks with any model

When you need a basic, clear SQL solution for an organizational hierarchy tree without deep performance considerations or advanced features.

prompt.txt

As a SQL developer, create a database solution for an employee hierarchy. Provide a `CREATE TABLE` statement for `{{employee_table_name}}` with `employee_id`, `employee_name`, and `manager_id`. Include `INSERT` statements for at least 10 sample employees, showing managers and direct reports, with one top-level employee having a NULL `manager_id`. Then, write a Recursive CTE query that starts from the top employee (NULL `manager_id`) and shows each employee's `employee_id`, `employee_name`, `manager_id`, `hierarchy_level` (starting at 1 for the root), and their `full_path` (e.g., 'CEO > VP > Employee'). Use standard SQL.

ProfessionalBest with claude

For database engineers requiring a comprehensive, production-ready SQL solution that includes DDL, test data, performance considerations, and index recommendations.

prompt.txt

As a database engineer specializing in SQL query optimization and hierarchical data structures.

### Context
You are tasked with modeling an organizational hierarchy from a flat employee table. The goal is to represent the 'reports to' structure as a tree, including each employee's depth within the hierarchy and their full organizational path. The solution must be portable across common SQL databases.

### Task
Develop a complete SQL solution using a Recursive Common Table Expression (CTE) to construct an organizational hierarchy. This solution must include:

1. **DDL (Data Definition Language)**: A `CREATE TABLE` statement for a sample `{{employee_table_name}}` with `employee_id`, `employee_name`, and `manager_id` columns. Ensure `manager_id` can be NULL for the top-level employee(s).
2. **Sample Data**: `INSERT` statements to populate the `{{employee_table_name}}` with at least 10 employees, demonstrating multiple levels of hierarchy and a clear root. Include a designated root employee with a NULL `manager_id` or a specific `{{root_employee_id}}`.
3. **Recursive CTE Query**: The core SQL query that:
* Starts from the specified `{{root_employee_id}}` or all employees with `NULL` `manager_id` if no specific root is provided.
* Recursively traverses the `employee_id` to `manager_id` relationship.
* For each employee, returns `employee_id`, `employee_name`, `manager_id`, `hierarchy_level` (depth), and `full_path` (e.g., "CEO > VP > Director > Employee").
4. **Expected Query Plan Notes**: Briefly describe the expected execution plan characteristics, focusing on how the recursive nature might impact performance and potential optimizations.
5. **Index Recommendation**: Suggest specific indexes that would improve the performance of this recursive CTE query.

### Constraints
* Use standard SQL syntax compatible with PostgreSQL, SQL Server, and MySQL 8.0+.
* The `full_path` should use a clear delimiter (e.g., ' > ').
* The `hierarchy_level` should start at 1 for the root(s).
* The solution must be presented as a single, executable SQL script block, with comments where appropriate.
* Prioritize clarity and maintainability alongside performance considerations.

### Output
Provide the complete SQL solution, including DDL, sample data, the recursive CTE query, expected query plan notes, and index recommendations, structured clearly.

Short VersionWorks with any model

When you need a quick, concise SQL solution for an organizational hierarchy tree, focusing on the core DDL, data, and recursive query.

prompt.txt

Generate a standard SQL solution for an organizational hierarchy using a Recursive CTE. Include `CREATE TABLE` for `{{employee_table_name}}` (employee_id, employee_name, manager_id), `INSERT` statements for sample data with a `NULL` manager_id root, and the recursive query. The query should return `employee_id`, `employee_name`, `manager_id`, `hierarchy_level` (starting at 1), and `full_path` (e.g., 'CEO > VP > Employee'). Provide this as a single script.

EnterpriseBest with chatgpt

For large organizations requiring a scalable, compliant, and auditable SQL hierarchy solution, considering enterprise-level concerns like data governance and large datasets.

prompt.txt

As a lead database architect for a large enterprise, develop a compliant and scalable SQL Recursive CTE solution for our organizational hierarchy. The solution must include DDL for `{{employee_table_name}}` (employee_id, employee_name, manager_id), sample data, and a robust recursive CTE query that derives `hierarchy_level` and `full_path`. Beyond standard requirements, include considerations for data governance, potential auditing requirements for changes in reporting structure, and discuss scalability implications for a large employee base (100k+ records). Provide detailed index strategies and a brief section on how this structure supports compliance reporting or HR analytics. Ensure the solution is portable across PostgreSQL, SQL Server, and MySQL 8.0+, prioritizing data integrity and maintainability for future expansions.

What you'll get

Expected output

-- DDL: Create the Employee table CREATE TABLE employees ( employee_id INT PRIMARY KEY, employee_name VARCHAR(100) NOT NULL, manager_id INT, CONSTRAINT fk_manager FOREIGN KEY (manager_id) REFERENCES employees(employee_id) );

-- Sample Data: Populate the Employee table INSERT INTO employees (employee_id, employee_name, manager_id) VALUES (1, 'Alice CEO', NULL), (2, 'Bob VP Sales', 1), (3, 'Charlie VP Marketing', 1), (4, 'David Sales Director', 2), (5, 'Eve Marketing Director', 3), (6, 'Frank Sales Rep', 4), (7, 'Grace Sales Rep', 4), (8, 'Heidi Marketing Specialist', 5), (9, 'Ivan Marketing Specialist', 5), (10, 'Judy Sales Rep', 2); -- Direct report to VP, not a director

-- Recursive CTE Query: Build the Organizational Hierarchy WITH RECURSIVE OrgHierarchy AS ( -- Anchor Member: Select the root employee(s) SELECT e.employee_id, e.employee_name, e.manager_id, 1 AS hierarchy_level, CAST(e.employee_name AS TEXT) AS full_path -- Use TEXT for path for portability FROM employees e WHERE e.manager_id IS NULL UNION ALL -- Recursive Member: Join employees to their managers SELECT e.employee_id, e.employee_name, e.manager_id, oh.hierarchy_level + 1 AS hierarchy_level, CAST(oh.full_path || ' > ' || e.employee_name AS TEXT) AS full_path -- Use || for concatenation (PostgreSQL/MySQL) FROM employees e INNER JOIN OrgHierarchy oh ON e.manager_id = oh.employee_id ) -- Final Selection from the CTE SELECT employee_id, employee_name, manager_id, hierarchy_level, full_path FROM OrgHierarchy ORDER BY full_path;

-- Expected Query Plan Notes: -- The query plan for a recursive CTE typically involves an initial scan for the anchor member, followed by iterative steps for the recursive member. Each iteration processes the results from the previous step, joining them back to the base table. This often manifests as a "Concatenation" or "Union All" operator combining the anchor and recursive parts. Performance is heavily dependent on efficient lookups for the manager_id in the recursive step. Without proper indexing, each recursive step could result in a full table scan, leading to significant performance degradation as the hierarchy depth or breadth increases. Temporary tables or worktables are often used internally by the database engine to store intermediate results during the recursion.

-- Index Recommendation: -- To optimize the recursive join operation, an index on the manager_id column is crucial. This allows the database to quickly find all direct reports for a given manager, making each recursive step efficient. CREATE INDEX idx_manager_id ON employees (manager_id);

Under the hood

Why this prompt works

This prompt structure yields effective SQL solutions by employing several key prompt engineering techniques. First, role priming establishes the persona of a "database engineer specializing in SQL query optimization and hierarchical data structures." This sets a clear expectation for the technical depth and specific expertise required in the output, guiding the model to generate high-quality, production-ready SQL rather than generic code.

Second, the prompt uses explicit constraints to define the scope and requirements precisely. Specifying compatibility with PostgreSQL, SQL Server, and MySQL 8.0+, along with details like the full_path delimiter and hierarchy_level starting point, ensures the generated SQL is both functional and adheres to specific formatting and portability standards. This prevents ambiguity and reduces the need for manual corrections.

Finally, the request for structured output across five distinct components (DDL, Sample Data, Recursive CTE Query, Expected Query Plan Notes, Index Recommendation) forces a comprehensive and organized response. This structured approach ensures all necessary elements for a complete, actionable solution are present, making the output immediately useful for a database engineer. A one-liner request would likely omit crucial context like DDL, test data, or performance considerations, resulting in an incomplete and less practical answer.

Model fit

Best AI models for this prompt

Claude

Claude models excel at understanding complex instructions and generating well-structured, coherent SQL. Its ability to follow detailed constraints for DDL, recursive CTE logic, path construction, and performance notes makes it a strong choice. It is generally reliable for producing portable SQL syntax. See the full Claude hub for deeper guidance.

ChatGPT

ChatGPT, particularly GPT-4, is proficient in generating intricate SQL queries and explanations. It handles the multi-part request well, providing DDL, data, the CTE, and analytical notes on query plans and indexing. Users should verify the specific SQL dialect nuances for their target database, as its output can sometimes be generic. See the full ChatGPT hub for deeper guidance.

Gemini

Gemini models are capable of generating correct and efficient SQL for complex tasks like recursive CTEs. Its strength lies in its reasoning for index recommendations and query plan considerations, often providing insightful suggestions. Ensure the generated SQL adheres strictly to the specified output format and column naming conventions. See the full Gemini hub for deeper guidance.

When to use

When you need to query a direct reporting structure, like for an HR application.
For building dynamic organization charts where depth and path are required.
To calculate the exact depth or full reporting path for each employee.
When analyzing reporting lines for compliance, audit, or internal communication purposes.
To propagate permissions or attributes down an organizational tree based on reporting structure.

When not to use

For extremely wide hierarchies (many direct reports) or very deep ones, due to potential performance impacts.
When the hierarchy is not strictly parent-child, such as with circular references or multiple direct managers.
If the dataset is massive (millions of employees) and real-time query performance is a strict requirement.
When a graph database is a more appropriate tool for complex, multi-faceted relationship modeling.
For simple flat lists where hierarchical context is not needed, as it adds unnecessary complexity.

Get more from it

Pro tips

1
Test the CTE with varying hierarchy depths and widths to understand its performance characteristics on different data structures.
2
Validate the output for orphan nodes (employees without a manager or root) to ensure data integrity before production use.
3
Experiment with the `MAXRECURSION` option in SQL Server to prevent runaway queries from unexpected data cycles.
4
Consider pre-calculating and storing the hierarchy in a dedicated table if the organizational structure is static and queried frequently.
5
Add specific `WHERE` clauses to the anchor member to analyze sub-trees, limiting the initial data processed for targeted queries.

Don't ship this

Common mistakes

✗ Infinite loops caused by circular references in `manager_id` data.
Fix — Implement a cycle detection mechanism within the CTE or use `MAXRECURSION` limits to prevent runaway queries.
✗ Missing an index on `manager_id` or `employee_id` columns.
Fix — Create a non-clustered index on `manager_id` and ensure `employee_id` is indexed for efficient joins.
✗ Inefficient string concatenation for the `full_path` column.
Fix — Use the database's native string concatenation function (e.g., `CONCAT` or `||`) for better performance.
✗ Not correctly handling multiple root employees in the hierarchy.
Fix — Ensure the anchor member of the CTE correctly identifies all top-level employees with `manager_id IS NULL`.
✗ Performance degradation on very large employee datasets.
Fix — Consider materializing the hierarchy into a separate table or exploring graph database solutions for extreme scale.

Frequently asked questions

Q.Can this CTE handle multiple root employees, such as co-CEOs?

Yes, the initial (anchor) member of the CTE is designed to select all employees where manager_id is NULL. This allows the query to correctly build multiple independent hierarchies if they exist.

Q.How can I optimize this solution for very large employee tables with many thousands of records?

For extremely large tables, consider persisting the hierarchy in a separate table, using a graph database, or implementing a custom hierarchy management solution outside of pure SQL for better performance.

Q.What happens if there are circular reporting structures in the employee data?

A circular reporting structure will cause the recursive CTE to enter an infinite loop. Most modern SQL databases offer mechanisms like MAXRECURSION (SQL Server) to prevent this, or you must clean your data.

Q.Is this SQL solution portable across all major relational databases?

The solution uses standard SQL recursive CTE syntax, which is widely supported by PostgreSQL, SQL Server, Oracle, and MySQL 8.0+. Minor syntax variations for string functions might require small adjustments.

Q.How does this approach compare to a materialized path for hierarchy storage?

Materialized paths store the full path directly, offering faster reads for path queries but slower updates. Recursive CTEs compute paths on the fly, making them suitable for dynamic hierarchies where data changes frequently.

Keep going

Related from the library

Related prompts

Browse more in Database or the full Coding hub.

Version 1.0Last reviewed July 20, 2026

Reviewed by PromptInFlow Editorial Team

SQL Recursive CTE for Organizational Hierarchy Tree

Claude

ChatGPT

Gemini

When to use

When not to use