Data Analyst Interview Questions & Answers: Set 12, HR & People Analytics

SQL

1. 1.Calculate monthly employee attrition rate by department.

   Hide answer

   Answer: Attrition rate = exits in the month divided by average headcount in the month, times 100. Assume employees(emp_id, dept, hire_date, exit_date). I build a month series, count exits per department per month, and divide by the average of start and end headcount. Trap: dividing by end-of-month headcount alone overstates attrition when the team is shrinking.

   WITH months AS (
     SELECT generate_series(DATE '2025-01-01', DATE '2025-12-01',
                            INTERVAL '1 month')::date AS m
   )
   SELECT m.m AS month, e.dept,
     COUNT(*) FILTER (WHERE e.exit_date >= m.m
       AND e.exit_date < m.m + INTERVAL '1 month') AS exits,
     ROUND(100.0 * COUNT(*) FILTER (WHERE e.exit_date >= m.m
       AND e.exit_date < m.m + INTERVAL '1 month')
      / NULLIF((COUNT(*) FILTER (WHERE e.hire_date < m.m
          AND (e.exit_date IS NULL OR e.exit_date >= m.m))
        + COUNT(*) FILTER (WHERE e.hire_date < m.m + INTERVAL '1 month'
          AND (e.exit_date IS NULL
               OR e.exit_date >= m.m + INTERVAL '1 month'))) / 2.0, 0)
     , 2) AS attrition_pct
   FROM months m CROSS JOIN employees e
   GROUP BY m.m, e.dept ORDER BY m.m, e.dept;

2. 2.Find employees who have not received a salary increase in 2 years.

   Hide answer

   Answer: Take each employee's latest salary revision and keep those where it is older than 2 years, or who never had a revision since joining. Assume employees(emp_id, name, hire_date, exit_date) and salary_history(emp_id, effective_date, salary). Include only active staff, and exclude new joiners with less than 2 years' service, otherwise they show up falsely.

   SELECT e.emp_id, e.name, MAX(s.effective_date) AS last_increase
   FROM employees e
   LEFT JOIN salary_history s ON s.emp_id = e.emp_id
   WHERE e.exit_date IS NULL
     AND e.hire_date <= CURRENT_DATE - INTERVAL '2 years'
   GROUP BY e.emp_id, e.name
   HAVING MAX(s.effective_date) IS NULL
       OR MAX(s.effective_date) < CURRENT_DATE - INTERVAL '2 years';