Employee Attrition Report by Department

MediumSQL · PostgreSQL

Problem

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.

Schema

departments

Column	Type	Description
department_idPK	INT	Unique department identifier
department_name	VARCHAR(100)	Department name
org	VARCHAR(100)	Meta organization name

employees

Column	Type	Description
employee_idPK	INT	Unique employee identifier
employee_name	VARCHAR(100)	Employee full name
department_id	INT	Department assignment
hire_date	DATE	Employee hire date
location	VARCHAR(100)	Primary office location

employment_events

Column	Type	Description
event_idPK	INT	Unique employment event identifier
employee_id	INT	Employee tied to the event
event_type	VARCHAR(50)	Type of event such as hire, transfer, or termination
event_date	DATE	Date of the event
reason	VARCHAR(100)	Reason associated with the event

Tablesdepartmentsemployeesemployment_events