People analytics: headcount, pay and org structure

HR wants to know which departments hold the most experience. Tenure is the time from hire_date to 2026-09-30, in years computed as days / 365.25. For each department that has at least one employee, return department name, headcount, and average tenure in years rounded to 1 decimal. Sort by average tenure highest first, then department name A to Z.

Onboarding load matters: teams full of new joiners need more support. For every department, including departments with no employees, return department name, headcount, number of employees hired on or after 2024-01-01, and those recent hires as a percentage of headcount rounded to 1 decimal. A department with no employees has headcount 0, recent hires 0 and NULL as percentage. Sort by department name A to Z.

For a pay-equity check, HR wants to see how much salaries differ between people with the same job title. Only titles held by at least two employees are meaningful. Return title, number of employees, lowest salary, highest salary and spread (highest minus lowest). Sort by spread highest first, then title A to Z.

Leadership is reviewing whether managers have too many or too few people. A person's direct reports are the employees whose manager_id is that person's id (direct only, not reports of reports). For every employee with at least one direct report, return the manager's name, title and number of direct reports. Sort by number of direct reports highest first, then name A to Z.

Pay compression happens when people earn almost as much as their manager. For every employee who has a manager, compute their salary as a percentage of their manager's salary. Return employee name, title, manager name and that percentage rounded to 1 decimal, only for employees at 80% or more of their manager's salary. Sort by percentage highest first, then employee name A to Z.

Compensation wants the average salary of people managers in each department. A people manager is an employee with at least one direct report, and each manager counts once regardless of team size; the department is the manager's own department. Return department name, number of managers and their average salary rounded to whole euros, for departments that have at least one manager. Sort by department name A to Z.

A candidate asks what a typical salary is in each team, and a few senior salaries pull the average up. For each department with at least one employee, return department name, headcount, average salary rounded to whole euros, and median salary. The median is the middle salary when the department's salaries are sorted; with an even headcount it is the average of the two middle salaries. Sort by department name A to Z.

The COO wants to see the shape of the org. The CEO (the employee with no manager) is layer 1, the CEO's direct reports are layer 2, their direct reports layer 3, and so on down every reporting chain. Return layer number, number of employees in that layer and their average salary rounded to whole euros. Sort by layer from top (1) to bottom.