<p>Recruiting teams want to know how long managers have been in their roles. </p> <p>Retrieves the average length of time managers have been in a position. </p> <p>Please write a query to retrieve the required data in the stored procedure. </p> <p>Table name = employee, you can check the table by running a query like <code>select top 10 * from employee</code> and press "Return Value" to see the results. </p> <p>manager_id refers to the employee’s direct manager. </p> <p>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. </p> <table class="s-table"> <thead> <tr> <th>mployee_id</th> <th>manager_id</th> <th>name</th> <th>time_in_position</th> </tr> </thead> <tbody> <tr> <td>8</td> <td>9</td> <td>Michael Kim</td> <td>6.20</td> </tr> <tr> <td>34</td> <td>35</td> <td>Peter Tan</td> <td>4.00</td> </tr> <tr> <td>21</td> <td>23</td> <td>Alice Li</td> <td>1.90</td> </tr> </tbody> </table> <p>Retrieves the average length of time managers have been in a position. </p>

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;