The Number of Employees Which Report to Each Employee SQL MySQL Leetcode Solution using GROUP BY

Published: 20 May 2024
on channel: CodeWis Technologies by Nuhman Paramban

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.

Watch video The Number of Employees Which Report to Each Employee SQL MySQL Leetcode Solution using GROUP BY online, duration hours minute second in high quality that is uploaded to the channel CodeWis Technologies by Nuhman Paramban 20 May 2024. Share the link to the video on social media so that your subscribers and friends will also watch this video. This video clip has been viewed 12 times and liked it 1 visitors.

1,014