Average tenure: Statistics for a manager’s tenure

Question

Recruiting teams want to know how long managers have been in their roles.

Retrieves the average length of time managers have been in a position.

Please write a query to retrieve the required data in the stored procedure.

Table name = employee, you can check the table by running a query like select top 10 * from employee and press "Return Value" to see the results.

manager_id refers to the employee’s direct manager.

Do not round the result (it needs to match the numbers in the expected output), make sure to give the column a name in the query output, such as time_in_position.

mployee_id	manager_id	name	time_in_position
8	9	Michael Kim	6.20
34	35	Peter Tan	4.00
21	23	Alice Li	1.90

Retrieves the average length of time managers have been in a position.

Answer 1

I assume that your "employee" table has a column called "start_date" that represents the start date of the employee's position.

SELECT AVG(DATEDIFF(MONTH, start_date, end_date)) AS average_time_in_position
FROM (
    SELECT e1.employee_id, e1.manager_id, e1.name, e1.start_date, COALESCE(e2.start_date, GETDATE()) AS end_date
    FROM employee e1
    LEFT JOIN employee e2 ON e1.employee_id = e2.manager_id
) AS manager_positions;