The Number of Employees Which Report to Each Employee SQL MySQL Leetcode Solution using GROUP BY
The Number of Employees Which Report to Each Employee SQL MySQL Leetcode Solution using GROUP BY
Challenge: Analyzing Employee Reports
Difficulty: Easy
Data:
A table named Employees stores information about employees and their reporting structure:
employee_id (int): Unique identifier for an employee (Primary Key).
name (varchar): Name of the employee.
reports_to (int): Foreign key referencing another employee_id. This indicates who the employee reports to (manager). Can be null if the employee has no manager.
age (int): Age of the employee.
Objective:
Identify and report information about managers within the company. A manager is defined as an employee with at least one other employee reporting to them.
Output:
A table with four columns:
employee_id (int): Unique identifier for the manager (same as input table).
name (varchar): Name of the manager.
reports_count (int): Number of employees who directly report to this manager.
average_age (int): Average age of the employees reporting to this manager, rounded to the nearest integer.
Explanation:
We need to find employees in the Employees table where reports_to is not null. For these managers, we need to count the number of employees with a matching reports_to value (their direct reports). Finally, calculate the average age of these direct reports, rounded to the nearest integer.