Write a query to return the department with the highest average tenure of employees.

Answer

Compute each employee's tenure, average it per department, and return only the single department with the highest average.

Solution

SELECT department_id, AVG(DATEDIFF(CURRENT_DATE, hire_date)) / 365.0 AS avg_tenure_years
FROM Employees
GROUP BY department_id
ORDER BY avg_tenure_years DESC
LIMIT 1;

Step by Step

1. DATEDIFF(CURRENT_DATE, hire_date) computes each employee's tenure in days.
2. Dividing by 365.0 converts that into an approximate tenure in years (a common simplification; leap years introduce only minor rounding drift).
3. AVG(...) grouped by department_id gives each department's average tenure.
4. Sorting descending and taking the top row returns the single department with the longest average tenure.

Alternative: Returning Ties Explicitly with RANK()

SELECT department_id, avg_tenure_years FROM (
  SELECT department_id,
         AVG(DATEDIFF(CURRENT_DATE, hire_date)) / 365.0 AS avg_tenure_years,
         RANK() OVER (ORDER BY AVG(DATEDIFF(CURRENT_DATE, hire_date)) DESC) AS rnk
  FROM Employees
  GROUP BY department_id
) x
WHERE rnk = 1;

If two departments could plausibly tie for the highest average tenure, RANK() returns both, while LIMIT 1 would arbitrarily pick just one and silently hide the tie.

Key Exam Traps

• Employees who left the company: If the table includes former employees with a termination_date, using CURRENT_DATE for everyone would overstate their tenure - the calculation should use COALESCE(termination_date, CURRENT_DATE) instead, unless the table only tracks current staff.
• Days-to-years conversion is an approximation: Dividing by exactly 365 ignores leap years; for precise tenure reporting, using a date-difference-in-years function (where the engine provides one) avoids compounding rounding error across many employees.
• Small departments skew averages: A department with only 1-2 long-tenured employees can top this ranking purely due to small sample size - worth flagging as a caveat alongside the raw answer, similar to the small-sample bias issue in other questions in this set.