Task
Meta's People Analytics team wants a simple attrition report by department. Write a SQL query that joins three tables to show headcount, attrition, and attrition rate for each department.
Requirements
- Use the
employees, departments, and employment_events tables.
- Return one row per department, including departments with no employees.
- Count:
- total employees assigned to the department
- employees who have at least one
termination event
- active employees with no termination event
- Calculate
attrition_rate as terminated employees divided by total employees, rounded to 2 decimal places.
- Order results by
attrition_rate descending, then department_name ascending.
Table Definitions
departments
| column | type | description |
|---|
| department_id | INT | Department identifier |
| department_name | VARCHAR(100) | Department name |
| org | VARCHAR(100) | Meta organization |
| | |
employees
| column | type | description |
|---|
| employee_id | INT | Employee identifier |
| employee_name | VARCHAR(100) | Employee name |
| department_id | INT | Employee's current department |
| hire_date | DATE | Hire date |
| location | VARCHAR(100) | Office location |
| | |
employment_events
| column | type | description |
|---|
| event_id | INT | Event identifier |
| employee_id | INT | Employee tied to the event |
| event_type | VARCHAR(50) | Event type such as hire, transfer, or termination |
| event_date | DATE | Event date |
| reason | VARCHAR(100) | Event reason |
Sample Data
departments
| department_id | department_name | org |
|---|
| 10 | Engineering | Infrastructure |
| 20 | Sales | Business |
| 30 | HR | Corporate |
employees
| employee_id | employee_name | department_id | hire_date | location |
|---|
| 101 | Ava Chen | 10 | 2021-03-15 | Menlo Park |
| 102 | Ben Patel | 20 | 2022-06-01 | New York |
| 103 | Carla Gomez | 10 | 2020-11-20 | London |
employment_events
| event_id | employee_id | event_type | event_date | reason |
|---|
| 1 | 101 | hire | 2021-03-15 | new hire |
| 2 | 101 | termination | 2024-01-10 | voluntary |
| 3 | 102 | hire | 2022-06-01 | new hire |
Expected Output
| department_name | total_employees | terminated_employees | active_employees | attrition_rate |
|---|
| HR | 2 | 1 | 1 | 0.50 |
| Sales | 3 | 1 | 2 | 0.33 |
| Engineering | 4 | 1 | 3 | 0.25 |
| Legal | 0 | 0 | 0 | 0.00 |
