Scenario SQL Question

Find Employees and Their Direct Reports Count

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

Problem Statement

For every employee, count how many people report DIRECTLY to them (0 if none). Return manager_id (the employee's id), manager_name, total_reports — ordered by total_reports DESC, manager_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