Interview Guides

Employee Attrition Report by Department | Dataford Interview Questions - Dataford - Ace your Interview

Employee Attrition Report by Department

Medium

SQL & Data Manipulation

Asked at 2 companies2JoinsAggregationsData Wrangling

Also asked at

Problem

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

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

employees

employee_id	employee_name	department_id	hire_date	location
104	Diego Ruiz	10	2023-01-09	Seattle
101	Ava Chen	10	2021-03-15	Menlo Park
108	Hugo Silva	30	2023-05-20	Austin
102	Ben Patel	20	2022-06-01	New York
110	Jia Li	40	2022-09-18	Singapore
103	Carla Gomez	10	2020-11-20	London
111	Kai Brown	40	2024-01-08
105	Elena Park	20	2021-08-30	Chicago
109	Iris Johnson	30	2020-02-14
106	Farah Ali	20	2024-02-12	Dublin
107	Grace Kim	10	2019-07-07	Menlo Park
112	Liam O'Neil		2023-11-01	Remote
1	Diego Ruiz	95	2021-05-26	Los Angeles
2	Carla Gomez	62	2022-01-31	Seattle
3	Theo Walker	78	2019-12-19	null
4	Victor Chen	48	2019-08-19	Mumbai
5	Grace Kim	16	2023-06-14	Los Angeles
6	Liam O'Neil	76	2020-12-04	Paris
7	Zane Cooper	68	2022-04-24
8	Victor Chen	99	2021-12-26	Mumbai
9	Quinn Taylor	16	2021-03-04	Washington DC
10	Elena Park	95	2023-12-06	Dublin
11	Bianca Rossi	58	2022-03-03	Washington DC
12	Maya Singh	10	2020-10-29	null
13	Ximena Cruz	80	2021-03-19	London
14	Uma Narang	96	2021-07-26	Los Angeles
15	Diego Ruiz	87	2019-11-17	Atlanta
16	Bianca Rossi	42	2019-09-24	London

departments

department_id	department_name	org
40	HR	Corporate
10	Engineering	Infrastructure
50	Legal	Corporate
20	Sales	Business
30	Marketing	Business
60	Finance
70	Operations	Corporate
80	Research	Reality Labs
1	Design	Partnerships
2	Security	Commerce
3	Procurement	Infrastructure
4	Facilities	Messaging
5	Content	Operations
6	Policy	Reality Labs
7	Product	Integrity
8	Research	null
9	Support	Legal
10	Reality Labs	Finance
11	Research	Infrastructure
12	Analytics	Product
13	IT	Ads
14	Support	Trust
15	Growth	Analytics
16	Policy	Reality Labs
17	Business Development	Research
18	Analytics	Security
19	Business Development	Corporate
20	Facilities	Business
21	Partnerships	null
22	Recruiting	null

employment_events

event_id	employee_id	event_type	event_date	reason
12	110	hire	2022-09-18	new hire
1	101	hire	2021-03-15	new hire
15	111	hire	2024-01-08	new hire
4	102	hire	2022-06-01	new hire
10	107	hire	2019-07-07	new hire
7	105	hire	2021-08-30	new hire
16	999	termination	2024-03-01	data error
9	106	hire	2024-02-12	new hire
11	109	hire	2020-02-14	new hire
14	110	termination	2024-04-10	voluntary
5	103	hire	2020-11-20	new hire
13	108	hire	2023-05-20	new hire
17		termination	2024-02-20	unknown
2	101	termination	2024-01-10	voluntary
18	103	promotion	2023-07-01	level change
3	101	termination	2024-01-11	duplicate record
8	105	termination	2024-02-05	involuntary
6	104	hire	2023-01-09	new hire
1	48	intern_end	2023-12-29	relocation
2	67	leave_of_absence	2020-03-03	medical leave
3	55	demotion	2022-07-26	manager request
4	35	location_change	2020-03-26	duplicate record
5	19	conversion	2021-05-14	relocation
6	32	demotion	2021-12-09	null
7	61	return_from_leave	2024-03-30	visa issue
8	49	intern_start	2020-12-15	voluntary
9	72	promotion	2022-04-12	relocation

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	5	1	4	0.20
Analytics	0	0	0	0.00
Business Development	0	0	0	0.00
Content	0	0	0	0.00
Design	0	0	0	0.00
Facilities	0	0	0	0.00
Finance	0	0	0	0.00
Growth	0	0	0	0.00
IT	0	0	0	0.00
Legal	0	0	0	0.00
Marketing	2	0	2	0.00
Operations	0	0	0	0.00
Partnerships	0	0	0	0.00
Policy	2	0	2	0.00
Procurement	0	0	0	0.00
Product	0	0	0	0.00
Recruiting	0	0	0	0.00
Research	1	0	1	0.00
Security	0	0	0	0.00
Support	0	0	0	0.00

Problem

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

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

employees

employee_id	employee_name	department_id	hire_date	location
104	Diego Ruiz	10	2023-01-09	Seattle
101	Ava Chen	10	2021-03-15	Menlo Park
108	Hugo Silva	30	2023-05-20	Austin
102	Ben Patel	20	2022-06-01	New York
110	Jia Li	40	2022-09-18	Singapore
103	Carla Gomez	10	2020-11-20	London
111	Kai Brown	40	2024-01-08
105	Elena Park	20	2021-08-30	Chicago
109	Iris Johnson	30	2020-02-14
106	Farah Ali	20	2024-02-12	Dublin
107	Grace Kim	10	2019-07-07	Menlo Park
112	Liam O'Neil		2023-11-01	Remote
1	Diego Ruiz	95	2021-05-26	Los Angeles
2	Carla Gomez	62	2022-01-31	Seattle
3	Theo Walker	78	2019-12-19	null
4	Victor Chen	48	2019-08-19	Mumbai
5	Grace Kim	16	2023-06-14	Los Angeles
6	Liam O'Neil	76	2020-12-04	Paris
7	Zane Cooper	68	2022-04-24
8	Victor Chen	99	2021-12-26	Mumbai
9	Quinn Taylor	16	2021-03-04	Washington DC
10	Elena Park	95	2023-12-06	Dublin
11	Bianca Rossi	58	2022-03-03	Washington DC
12	Maya Singh	10	2020-10-29	null
13	Ximena Cruz	80	2021-03-19	London
14	Uma Narang	96	2021-07-26	Los Angeles
15	Diego Ruiz	87	2019-11-17	Atlanta
16	Bianca Rossi	42	2019-09-24	London

departments

department_id	department_name	org
40	HR	Corporate
10	Engineering	Infrastructure
50	Legal	Corporate
20	Sales	Business
30	Marketing	Business
60	Finance
70	Operations	Corporate
80	Research	Reality Labs
1	Design	Partnerships
2	Security	Commerce
3	Procurement	Infrastructure
4	Facilities	Messaging
5	Content	Operations
6	Policy	Reality Labs
7	Product	Integrity
8	Research	null
9	Support	Legal
10	Reality Labs	Finance
11	Research	Infrastructure
12	Analytics	Product
13	IT	Ads
14	Support	Trust
15	Growth	Analytics
16	Policy	Reality Labs
17	Business Development	Research
18	Analytics	Security
19	Business Development	Corporate
20	Facilities	Business
21	Partnerships	null
22	Recruiting	null

employment_events

event_id	employee_id	event_type	event_date	reason
12	110	hire	2022-09-18	new hire
1	101	hire	2021-03-15	new hire
15	111	hire	2024-01-08	new hire
4	102	hire	2022-06-01	new hire
10	107	hire	2019-07-07	new hire
7	105	hire	2021-08-30	new hire
16	999	termination	2024-03-01	data error
9	106	hire	2024-02-12	new hire
11	109	hire	2020-02-14	new hire
14	110	termination	2024-04-10	voluntary
5	103	hire	2020-11-20	new hire
13	108	hire	2023-05-20	new hire
17		termination	2024-02-20	unknown
2	101	termination	2024-01-10	voluntary
18	103	promotion	2023-07-01	level change
3	101	termination	2024-01-11	duplicate record
8	105	termination	2024-02-05	involuntary
6	104	hire	2023-01-09	new hire
1	48	intern_end	2023-12-29	relocation
2	67	leave_of_absence	2020-03-03	medical leave
3	55	demotion	2022-07-26	manager request
4	35	location_change	2020-03-26	duplicate record
5	19	conversion	2021-05-14	relocation
6	32	demotion	2021-12-09	null
7	61	return_from_leave	2024-03-30	visa issue
8	49	intern_start	2020-12-15	voluntary
9	72	promotion	2022-04-12	relocation

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	5	1	4	0.20
Analytics	0	0	0	0.00
Business Development	0	0	0	0.00
Content	0	0	0	0.00
Design	0	0	0	0.00
Facilities	0	0	0	0.00
Finance	0	0	0	0.00
Growth	0	0	0	0.00
IT	0	0	0	0.00
Legal	0	0	0	0.00
Marketing	2	0	2	0.00
Operations	0	0	0	0.00
Partnerships	0	0	0	0.00
Policy	2	0	2	0.00
Procurement	0	0	0	0.00
Product	0	0	0	0.00
Recruiting	0	0	0	0.00
Research	1	0	1	0.00
Security	0	0	0	0.00
Support	0	0	0	0.00

Practice SQL

PostgreSQL

Open on desktop for the full SQL editor with syntax highlighting and autocomplete.

Up next

Analyze Employee Turnover Trends for Workforce PlanningMedium

Validate Workforce Data Across SystemsMedium Find Employee Manager RelationshipsMedium

Next question

Employee Attrition Report by Department

Medium

SQL & Data Manipulation

Asked at 2 companies2JoinsAggregationsData Wrangling

Also asked at

Problem

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

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

employees

employee_id	employee_name	department_id	hire_date	location
104	Diego Ruiz	10	2023-01-09	Seattle
101	Ava Chen	10	2021-03-15	Menlo Park
108	Hugo Silva	30	2023-05-20	Austin
102	Ben Patel	20	2022-06-01	New York
110	Jia Li	40	2022-09-18	Singapore
103	Carla Gomez	10	2020-11-20	London
111	Kai Brown	40	2024-01-08
105	Elena Park	20	2021-08-30	Chicago
109	Iris Johnson	30	2020-02-14
106	Farah Ali	20	2024-02-12	Dublin
107	Grace Kim	10	2019-07-07	Menlo Park
112	Liam O'Neil		2023-11-01	Remote
1	Diego Ruiz	95	2021-05-26	Los Angeles
2	Carla Gomez	62	2022-01-31	Seattle
3	Theo Walker	78	2019-12-19	null
4	Victor Chen	48	2019-08-19	Mumbai
5	Grace Kim	16	2023-06-14	Los Angeles
6	Liam O'Neil	76	2020-12-04	Paris
7	Zane Cooper	68	2022-04-24
8	Victor Chen	99	2021-12-26	Mumbai
9	Quinn Taylor	16	2021-03-04	Washington DC
10	Elena Park	95	2023-12-06	Dublin
11	Bianca Rossi	58	2022-03-03	Washington DC
12	Maya Singh	10	2020-10-29	null
13	Ximena Cruz	80	2021-03-19	London
14	Uma Narang	96	2021-07-26	Los Angeles
15	Diego Ruiz	87	2019-11-17	Atlanta
16	Bianca Rossi	42	2019-09-24	London

departments

department_id	department_name	org
40	HR	Corporate
10	Engineering	Infrastructure
50	Legal	Corporate
20	Sales	Business
30	Marketing	Business
60	Finance
70	Operations	Corporate
80	Research	Reality Labs
1	Design	Partnerships
2	Security	Commerce
3	Procurement	Infrastructure
4	Facilities	Messaging
5	Content	Operations
6	Policy	Reality Labs
7	Product	Integrity
8	Research	null
9	Support	Legal
10	Reality Labs	Finance
11	Research	Infrastructure
12	Analytics	Product
13	IT	Ads
14	Support	Trust
15	Growth	Analytics
16	Policy	Reality Labs
17	Business Development	Research
18	Analytics	Security
19	Business Development	Corporate
20	Facilities	Business
21	Partnerships	null
22	Recruiting	null

employment_events

event_id	employee_id	event_type	event_date	reason
12	110	hire	2022-09-18	new hire
1	101	hire	2021-03-15	new hire
15	111	hire	2024-01-08	new hire
4	102	hire	2022-06-01	new hire
10	107	hire	2019-07-07	new hire
7	105	hire	2021-08-30	new hire
16	999	termination	2024-03-01	data error
9	106	hire	2024-02-12	new hire
11	109	hire	2020-02-14	new hire
14	110	termination	2024-04-10	voluntary
5	103	hire	2020-11-20	new hire
13	108	hire	2023-05-20	new hire
17		termination	2024-02-20	unknown
2	101	termination	2024-01-10	voluntary
18	103	promotion	2023-07-01	level change
3	101	termination	2024-01-11	duplicate record
8	105	termination	2024-02-05	involuntary
6	104	hire	2023-01-09	new hire
1	48	intern_end	2023-12-29	relocation
2	67	leave_of_absence	2020-03-03	medical leave
3	55	demotion	2022-07-26	manager request
4	35	location_change	2020-03-26	duplicate record
5	19	conversion	2021-05-14	relocation
6	32	demotion	2021-12-09	null
7	61	return_from_leave	2024-03-30	visa issue
8	49	intern_start	2020-12-15	voluntary
9	72	promotion	2022-04-12	relocation

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	5	1	4	0.20
Analytics	0	0	0	0.00
Business Development	0	0	0	0.00
Content	0	0	0	0.00
Design	0	0	0	0.00
Facilities	0	0	0	0.00
Finance	0	0	0	0.00
Growth	0	0	0	0.00
IT	0	0	0	0.00
Legal	0	0	0	0.00
Marketing	2	0	2	0.00
Operations	0	0	0	0.00
Partnerships	0	0	0	0.00
Policy	2	0	2	0.00
Procurement	0	0	0	0.00
Product	0	0	0	0.00
Recruiting	0	0	0	0.00
Research	1	0	1	0.00
Security	0	0	0	0.00
Support	0	0	0	0.00

Problem

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

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

employees

employee_id	employee_name	department_id	hire_date	location
104	Diego Ruiz	10	2023-01-09	Seattle
101	Ava Chen	10	2021-03-15	Menlo Park
108	Hugo Silva	30	2023-05-20	Austin
102	Ben Patel	20	2022-06-01	New York
110	Jia Li	40	2022-09-18	Singapore
103	Carla Gomez	10	2020-11-20	London
111	Kai Brown	40	2024-01-08
105	Elena Park	20	2021-08-30	Chicago
109	Iris Johnson	30	2020-02-14
106	Farah Ali	20	2024-02-12	Dublin
107	Grace Kim	10	2019-07-07	Menlo Park
112	Liam O'Neil		2023-11-01	Remote
1	Diego Ruiz	95	2021-05-26	Los Angeles
2	Carla Gomez	62	2022-01-31	Seattle
3	Theo Walker	78	2019-12-19	null
4	Victor Chen	48	2019-08-19	Mumbai
5	Grace Kim	16	2023-06-14	Los Angeles
6	Liam O'Neil	76	2020-12-04	Paris
7	Zane Cooper	68	2022-04-24
8	Victor Chen	99	2021-12-26	Mumbai
9	Quinn Taylor	16	2021-03-04	Washington DC
10	Elena Park	95	2023-12-06	Dublin
11	Bianca Rossi	58	2022-03-03	Washington DC
12	Maya Singh	10	2020-10-29	null
13	Ximena Cruz	80	2021-03-19	London
14	Uma Narang	96	2021-07-26	Los Angeles
15	Diego Ruiz	87	2019-11-17	Atlanta
16	Bianca Rossi	42	2019-09-24	London

departments

department_id	department_name	org
40	HR	Corporate
10	Engineering	Infrastructure
50	Legal	Corporate
20	Sales	Business
30	Marketing	Business
60	Finance
70	Operations	Corporate
80	Research	Reality Labs
1	Design	Partnerships
2	Security	Commerce
3	Procurement	Infrastructure
4	Facilities	Messaging
5	Content	Operations
6	Policy	Reality Labs
7	Product	Integrity
8	Research	null
9	Support	Legal
10	Reality Labs	Finance
11	Research	Infrastructure
12	Analytics	Product
13	IT	Ads
14	Support	Trust
15	Growth	Analytics
16	Policy	Reality Labs
17	Business Development	Research
18	Analytics	Security
19	Business Development	Corporate
20	Facilities	Business
21	Partnerships	null
22	Recruiting	null

employment_events

event_id	employee_id	event_type	event_date	reason
12	110	hire	2022-09-18	new hire
1	101	hire	2021-03-15	new hire
15	111	hire	2024-01-08	new hire
4	102	hire	2022-06-01	new hire
10	107	hire	2019-07-07	new hire
7	105	hire	2021-08-30	new hire
16	999	termination	2024-03-01	data error
9	106	hire	2024-02-12	new hire
11	109	hire	2020-02-14	new hire
14	110	termination	2024-04-10	voluntary
5	103	hire	2020-11-20	new hire
13	108	hire	2023-05-20	new hire
17		termination	2024-02-20	unknown
2	101	termination	2024-01-10	voluntary
18	103	promotion	2023-07-01	level change
3	101	termination	2024-01-11	duplicate record
8	105	termination	2024-02-05	involuntary
6	104	hire	2023-01-09	new hire
1	48	intern_end	2023-12-29	relocation
2	67	leave_of_absence	2020-03-03	medical leave
3	55	demotion	2022-07-26	manager request
4	35	location_change	2020-03-26	duplicate record
5	19	conversion	2021-05-14	relocation
6	32	demotion	2021-12-09	null
7	61	return_from_leave	2024-03-30	visa issue
8	49	intern_start	2020-12-15	voluntary
9	72	promotion	2022-04-12	relocation

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	5	1	4	0.20
Analytics	0	0	0	0.00
Business Development	0	0	0	0.00
Content	0	0	0	0.00
Design	0	0	0	0.00
Facilities	0	0	0	0.00
Finance	0	0	0	0.00
Growth	0	0	0	0.00
IT	0	0	0	0.00
Legal	0	0	0	0.00
Marketing	2	0	2	0.00
Operations	0	0	0	0.00
Partnerships	0	0	0	0.00
Policy	2	0	2	0.00
Procurement	0	0	0	0.00
Product	0	0	0	0.00
Recruiting	0	0	0	0.00
Research	1	0	1	0.00
Security	0	0	0	0.00
Support	0	0	0	0.00

Practice SQL

PostgreSQL

Open on desktop for the full SQL editor with syntax highlighting and autocomplete.

Up next

Analyze Employee Turnover Trends for Workforce PlanningMedium

Validate Workforce Data Across SystemsMedium Find Employee Manager RelationshipsMedium

Next question