The Number of Employees Which Report to Each Employee - Problem

You are given a table Employees containing information about employees and their managers.

Table: Employees

Column NameType
employee_idint
namevarchar
reports_toint
ageint

employee_id is the primary key for this table. This table contains information about the employees and the id of the manager they report to. Some employees do not report to anyone (reports_to is null).

For this problem, we will consider a manager an employee who has at least 1 other employee reporting to them.

Write a solution to report the employee_id and name of all managers, the number of employees who report directly to them, and the average age of the reports rounded to the nearest integer.

Return the result table ordered by employee_id.

Table Schema

Employees
Column Name Type Description
employee_id PK int Unique identifier for each employee
name varchar Employee name
reports_to int Employee ID of the manager this employee reports to (NULL if no manager)
age int Employee age
Primary Key: employee_id
Note: Some employees do not report to anyone (reports_to is null). A manager is defined as having at least 1 direct report.

Input & Output

Example 1 — Basic Manager-Report Structure
Input Table:
employee_id name reports_to age
9 Hercy 43
6 Alice 9 41
4 Bob 9 36
2 John 20
Output:
employee_id name reports_count average_age
9 Hercy 2 39
💡 Note:

Employee 9 (Hercy) is a manager with 2 direct reports: Alice (age 41) and Bob (age 36). The average age is (41 + 36) / 2 = 38.5, rounded to 39. Employee 2 (John) has no reports, so is not included.
Example 2 — Multiple Managers
Input Table:
employee_id name reports_to age
1 Boss 50
2 Manager1 1 40
3 Manager2 1 35
4 Employee1 2 25
5 Employee2 2 30
Output:
employee_id name reports_count average_age
1 Boss 2 38
2 Manager1 2 28
💡 Note:

Boss (ID 1) manages 2 people with average age (40 + 35) / 2 = 37.5 → 38. Manager1 (ID 2) manages 2 people with average age (25 + 30) / 2 = 27.5 → 28. Manager2 has no direct reports.

Constraints

  • 1 ≤ employee_id ≤ 10^4
  • 1 ≤ name.length ≤ 20
  • 1 ≤ age ≤ 100
  • reports_to can be null for employees with no manager

Visualization

Tap to expand
Manager Report Analysis OverviewInput: Employee Hierarchyemp_idnamereports_toage9Hercynull436Alice9414Bob9362Johnnull20SELF-JOIN+ GROUP BYOutput: Manager Statsemployee_idnamereports_countaverage_age9Hercy239Reports highlighted in blue report to manager ID 9Average age: (41 + 36) / 2 = 38.5 → 39 (rounded)
Understanding the Visualization
1
Input
Employee hierarchy with reports_to relationships
2
Self-Join
Connect managers to their direct reports
3
Output
Manager statistics with report count and average age
Key Takeaway
🎯 Key Insight: Use self-joins when you need to connect rows within the same table based on relationships

Related Problems

Asked in
Amazon 23 Microsoft 18 Apple 15
28.5K Views
Medium Frequency
~12 min Avg. Time
892 Likes
Ln 1, Col 1
Smart Actions
💡 Explanation
AI Ready
💡 Suggestion Tab to accept Esc to dismiss
// Output will appear here after running code
Code Editor Closed
Click the red button to reopen