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
Employee Reporting Structure Analysis INPUT: Employees Table id name reports_to age 1 Mike 3 30 2 Sarah 3 28 3 Jane 9 35 4 Tom 3 32 9 Boss NULL 45 Reporting Hierarchy: Boss(9) Jane(3) Mike(1) Sarah(2) Tom(4) ALGORITHM STEPS 1 Self-Join Table Join Employees e1 with e2 WHERE e2.reports_to = e1.id 2 Group by Manager GROUP BY e1.employee_id, e1.name 3 Aggregate Functions COUNT(*) for reports_count ROUND(AVG(age)) for avg_age 4 Order Results ORDER BY employee_id SELECT e1.employee_id, e1.name, COUNT(*) AS reports_count, ROUND(AVG(e2.age)) AS avg_age FROM Employees e1 JOIN Employees e2 ON ... GROUP BY ... ORDER BY ... FINAL RESULT emp_id name reports avg_age 3 Jane 3 30 9 Boss 1 35 Jane (id=3) manages: Mike(30) + Sarah(28) + Tom(32) Avg = (30+28+32)/3 = 30 Boss (id=9) manages: Jane(35) Avg = 35/1 = 35 OK - 2 Managers Found Key Insight: Self-join identifies manager-employee pairs by matching reports_to with employee_id. Only employees with at least one report appear in results. ROUND(AVG()) ensures integer average ages as required. TutorialsPoint - The Number of Employees Which Report to Each Employee | Optimal Solution

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