Recursive Organization Depth and Reporting Path

Traverse the organization from every root and return each unit's depth, root unit, and full reporting path. Roots have depth 0. The hierarchy has arbitrary depth, so use WITH RECURSIVE. Return unit_id, unit_name, depth, reporting_path and root_manager. Return columns: unit_id, unit_name, depth, reporting_path, root_manager.

Preparing the interactive SQL workspace...

Challenge overview

What you will practice

Traverse the organization from every root and return each unit's depth, root unit, and full reporting path. Roots have depth 0. The hierarchy has arbitrary depth, so use WITH RECURSIVE. Return unit_id, unit_name, depth, reporting_path and root_manager. Return columns: unit_id, unit_name, depth, reporting_path, root_manager.

Difficulty: Expert
SQL concept: CTE