The Number of Employees Which Report to Each Employee | Leetcode 1731

Welcome to PlayBook Library. This comprehensive study note covers LeetCode Problem 1731: "The Number of Employees Which Report to Each Employee", marking our 30th milestone in the Crack SQL Interview in 50 Questions series. This chapter transitions us into advanced SELECT and JOIN operations, focusing on self-joins, conditional relational filtering, aggregations, and data rounding within relational databases.

1. Problem Statement & Business Context

In enterprise database management, hierarchical structures (such as organizational charts where employees report to managers) are routinely modeled using self-referencing tables.

We are given a single relational table named Employees containing information about employees and the specific managers they report to. Our objective is to generate a report containing:

• The Employee ID of each manager.
• The Name of each manager.
• The Number of Employees who report directly to that manager (reports_count).
• The Average Age of those direct reports, rounded to the nearest integer (average_age).

The output must be ordered ascendingly by the manager's employee_id.

2. Table Schema & Input Structure

The Employees table contains four columns, with employee_id serving as the primary key.

Defining a "Manager"

A critical business rule defined in the prompt:

Note: An employee is classified as a Manager if and only if at least one other employee reports directly to them. Employees who do not manage anyone, or top-level executives whose reports_to is NULL (and who have no subordinates), are excluded from the manager report.

3. Real-Life Mental Model: The Corporate Org Chart

To visualize this query, imagine a corporate office directory:

• Harsimran (ID 9) has two employees (Alice and Bob) pointing to her via reports_to = 9. Therefore, Harsimran is a Manager.
• Alice (ID 6) and Bob (ID 4) report to Harsimran, so they are subordinates, not managers.
• Winston (ID 2) reports to no one (reports_to IS NULL), and no one reports to him. Thus, Winston is not a manager.

4. Naive vs. Better Approach: Why Self-Joins?

The Naive Trap

Attempting to solve this using a single scan of the table makes it difficult to simultaneously inspect an employee's profile and aggregate the properties of their subordinates. We cannot isolate the manager's attributes from the subordinates' attributes without linking the table to itself.

The Better Approach: Self-Join

We treat the Employees table as two distinct logical entities using table aliases:

1. e1: Represents the Manager table.
2. e2: Represents the Subordinate (Reports) table.

By joining e1 and e2 where e1.employee_id = e2.reports_to, we create a unified dataset that pairs every manager with all of their direct reports.

5. Step-by-Step Query Construction

Let us build the SQL query incrementally, validating each clause.

Step 1: Performing the Self-Join

We join the table to itself to match managers with their reports:

SELECT *
FROM Employees e1
INNER JOIN Employees e2
  ON e1.employee_id = e2.reports_to;

Note: We use an INNER JOIN because we only want rows where an employee actually reports to a valid manager. Top-level employees with no reports will automatically be filtered out, satisfying our manager condition.

Step 2: Selecting Required Columns & Applying Aggregations

We extract the manager's ID and name (e1), count the number of subordinate IDs (e2), and calculate the average age of the subordinates (e2):

SELECT 
    e1.employee_id,
    e1.name,
    COUNT(e2.employee_id) AS reports_count,
    ROUND(AVG(e2.age)) AS average_age
FROM Employees e1
INNER JOIN Employees e2
  ON e1.employee_id = e2.reports_to

Step 3: Grouping and Ordering

Because we are using aggregate functions (COUNT and AVG), we must group the result set by the non-aggregated columns (e1.employee_id and e1.name). Finally, we apply ORDER BY as required by the problem statement.

6. Complete Production-Ready SQL Query

Here is the final, optimized SQL solution:

SELECT 
    e1.employee_id,
    e1.name,
    COUNT(e2.employee_id) AS reports_count,
    ROUND(AVG(e2.age)) AS average_age
FROM Employees e1
INNER JOIN Employees e2
  ON e1.employee_id = e2.reports_to
GROUP BY 
    e1.employee_id, 
    e1.name
ORDER BY 
    e1.employee_id;

7. Walkthrough of Example Execution

Given the sample data:

Execution Trace:

1. Self-Join Match:
   • e1 row (9, Harsimran, NULL, 43) matches e2 row (6, Alice, 9, 41).
   • e1 row (9, Harsimran, NULL, 43) matches e2 row (4, Bob, 9, 36).
2. Grouping: Rows are grouped by Manager ID 9 (Harsimran).
3. Aggregation:
   • reports_count: COUNT(6, 4) evaluates to 2.
   • average_age: AVG(41, 36) evaluates to 38.5.
4. Rounding: ROUND(38.5) converts to 39 (or database-dependent nearest integer rounding, resulting in 39).

Final Output Result Set:

8. Summary and Key Takeaways

• Self-Join Pattern: Use self-joins (e1 and e2) when querying hierarchical or parent-child relationships within the exact same database table.
• Inner Join Filtering: An INNER JOIN naturally enforces the rule that managers must have at least one report, dropping unmatched records automatically.
• Aggregation Pairing: Whenever aggregate functions like COUNT() or AVG() are utilized, all selected non-aggregated columns must be included in the GROUP BY clause.
• Data Precision via Rounding: The SQL ROUND() function without a decimal parameter rounds floating-point averages (AVG) to the nearest whole integer.
• Ordering Compliance: Always conclude analytical reporting queries with an ORDER BY clause matching the interviewer's or problem specification's exact sorting criteria.