Scenario SQL Question

Hierarchy Level and Team Size for Each Employee

Build mastery in Self Joins Hierarchical with this scenario-based SQL challenge and live execution support.

Problem Statement

For every employee compute their hierarchy_level (CEO=1) AND team_size (count of direct reports). Use a recursive CTE for level, then a LEFT JOIN to count reports. Return employee_id, employee_name, hierarchy_level, team_size — ordered by hierarchy_level, employee_id.

Relevant Tables

employees

SQL Concepts Used in This Scenario

Strengthen your understanding with these targeted learning topics:

Self JoinsJoins & Relationships
Recursive CTEs for Hierarchical DataSubqueries & CTEs
Common Table ExpressionsSubqueries & CTEs