Employee Attrition Report by Department | Dataford Interview Questions

Your question is Employee Attrition Report by Department. Start with the requirements and the three tables on the right.

Run and submit as often as you like. When you're ready, talk me through your approach or go straight to the code.

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