Masar Holding HR

SELECT d.department_name, COUNT(e.employee_id) AS headcount
FROM departments AS d
LEFT JOIN employees AS e
  ON e.department_id = d.department_id
 AND e.hire_date <= DATE '2025-12-31'
 AND (e.termination_date IS NULL OR e.termination_date >= DATE '2025-12-31')
GROUP BY d.department_name
ORDER BY headcount DESC;

SELECT DISTINCT ON (s.employee_id)
       e.employee_id, e.first_name, e.last_name, s.effective_date, s.base_salary,
       s.base_salary + s.housing_allowance + s.transport_allowance AS total_monthly
FROM salary_history AS s
JOIN employees AS e ON e.employee_id = s.employee_id
WHERE e.termination_date IS NULL
ORDER BY s.employee_id, s.effective_date DESC;

SELECT m.employee_id, m.first_name || ' ' || m.last_name AS manager, COUNT(r.employee_id) AS direct_reports
FROM employees AS m
JOIN employees AS r ON r.manager_id = m.employee_id AND r.termination_date IS NULL
WHERE m.termination_date IS NULL
GROUP BY m.employee_id, manager
ORDER BY direct_reports DESC;

WITH RECURSIVE chart AS (
  SELECT employee_id, manager_id, first_name || ' ' || last_name AS name, 1 AS level
  FROM employees WHERE manager_id IS NULL
  UNION ALL
  SELECT e.employee_id, e.manager_id, e.first_name || ' ' || e.last_name, c.level + 1
  FROM employees e JOIN chart c ON e.manager_id = c.employee_id
  WHERE e.termination_date IS NULL
)
SELECT level, COUNT(*) AS people FROM chart GROUP BY level ORDER BY level;

SELECT EXTRACT(YEAR FROM termination_date) AS year, termination_reason, COUNT(*) AS leavers
FROM employees
WHERE termination_date IS NOT NULL
GROUP BY 1, 2
ORDER BY 1, 3 DESC;