Real-World Scenario: HR Analytics

SELECT name, hire_date,
       DATEDIFF(CURDATE(), hire_date) / 365 AS years_employed
FROM employees
WHERE DATEDIFF(CURDATE(), hire_date) > 365 * 5
ORDER BY hire_date;

Tenure analysis — useful for retention studies, long-service awards, and attrition risk modelling.

SELECT
  COUNT(CASE WHEN exit_date >= DATE_SUB(CURDATE(), INTERVAL 12 MONTH)
             AND exit_date IS NOT NULL THEN 1 END) AS leavers,
  COUNT(*) AS total_employees,
  ROUND(
    100.0 * COUNT(CASE WHEN exit_date >= DATE_SUB(CURDATE(), INTERVAL 12 MONTH)
                       AND exit_date IS NOT NULL THEN 1 END)
    / COUNT(*), 2
  ) AS attrition_pct
FROM employees;

Attrition rate = leavers in period / total headcount * 100. A critical HR metric. Real implementations are more nuanced (use average headcount as denominator) but this is the interview version.

SELECT manager_id,
       COUNT(*) AS direct_reports
FROM employees
WHERE manager_id IS NOT NULL
GROUP BY manager_id
HAVING COUNT(*) > 5
ORDER BY direct_reports DESC;

Span of control analysis. JOIN to the employees table to get manager names: JOIN employees m ON e.manager_id = m.id.

SELECT department,
  AVG(CASE WHEN gender = 'Male' THEN salary END) AS avg_male,
  AVG(CASE WHEN gender = 'Female' THEN salary END) AS avg_female,
  AVG(CASE WHEN gender = 'Male' THEN salary END)
  - AVG(CASE WHEN gender = 'Female' THEN salary END) AS pay_gap
FROM employees
GROUP BY department
HAVING pay_gap > 0
ORDER BY pay_gap DESC;

Conditional AVG per gender. HAVING pay_gap > 0 shows departments where men earn more. This type of equity analysis is required in ESG reporting.

WITH months AS (
  SELECT DATE_FORMAT(CURDATE(), '%Y-%m-01') AS month_start
  -- Generate all month starts for this year (simplified)
)
SELECT month_start,
       COUNT(*) AS active_headcount
FROM employees, months
WHERE hire_date <= month_start
  AND (exit_date IS NULL OR exit_date > month_start)
GROUP BY month_start
ORDER BY month_start;

Headcount on a given date = hired before date AND (not exited OR exited after date). This pattern builds workforce trend charts used in HR dashboards.