Scenario SQL Question

Organization Hierarchy With Subordinate Counts (Recursive CTE)

Build mastery in Ctes Window with this scenario-based SQL challenge and live execution support.

Problem Statement

Build the org hierarchy with a recursive CTE. For each employee show their org_level (CEO = 1), the full management_path (CEO -> … -> employee), and total_subordinates (everyone in their reporting chain, direct + indirect). Return employee_id, first_name, last_name, org_level, management_path, total_subordinates — ordered by org_level, employee_id.

Relevant Tables

employees

SQL Concepts Used in This Scenario

Strengthen your understanding with these targeted learning topics:

Common Table ExpressionsSubqueries & CTEs
Window Functions FundamentalsWindow Functions
Running Totals & Moving AveragesWindow Functions