DB Exercises
0
← Exercises/
CTEs•6 of 12
PrevNext
CTEsAdvancedPRO

Recursive Employee Reporting Chain & Hierarchy Depth

Scenario

Enterprise org design mandates calculating management span and chain-of-command depth. Using a Recursive CTE, traverse the corporate org hierarchy to determine every employee's depth relative to the CEO (Level 1).

Task
Write a Recursive CTE (`WITH RECURSIVE org_tree AS (...)`) to compute the reporting depth for every employee: - The CEO has `manager_id IS NULL` and depth level **1**. - Each subordinate's depth is `parent.depth_level + 1`. Return: 1. `emp_id` 2. `emp_name` 3. `depth_level` Order results by `depth_level` ascending, then by `emp_id` ascending.
Expected Output Format

Employee hierarchy showing reporting depth distance from the executive level.

Input Tables & Sample Data0 tables
Loading database schema...
Need a hint?
query.sql|Local Sandbox
Loading SQL editor...
Click "Run" or press ⌘ + Enter to execute your query

About the “Recursive Employee Reporting Chain & Hierarchy Depth” SQL Problem

Business Scenario

Enterprise org design mandates calculating management span and chain-of-command depth. Using a Recursive CTE, traverse the corporate org hierarchy to determine every employee's depth relative to the CEO (Level 1).

Expected Output & Criteria

Employee hierarchy showing reporting depth distance from the executive level.

Database Table Schema (PostgreSQL DDL)

PostgreSQL 16 WASM

This challenge executes directly against the following relational schema in your browser:

CREATE TABLE corporate_hierarchy (
        emp_id INT PRIMARY KEY,
        emp_name VARCHAR(100) NOT NULL,
        manager_id INT REFERENCES corporate_hierarchy(emp_id)
      );

Target SQL Clauses & Operators

WITHRECURSIVEUNION ALL

Want more CTEs practice?

Explore the full syllabus of 12 exercises, syntax guides, and interview pitfalls.

View CTEs Track→