EasyHR10 min

Headcount by department

JOINs GROUP BY

Challenge

Count current employees in each department.

Business context

Finance is tying the headcount workbook to the department list. Every department with people should appear once.

Instructions

Return department and headcount. Count employees per department. Sort by headcount descending, then department name.

Schema

departments

id INTEGER · primary key
name TEXT
location TEXT

employees

id INTEGER · primary key
name TEXT
email TEXT
department_id INTEGER
manager_id INTEGER · employees.id, nullable
hire_date TEXT
title TEXT

salaries

id INTEGER · primary key
employee_id INTEGER
amount INTEGER · annual salary in dollars
effective_date TEXT

job_history

id INTEGER · primary key
employee_id INTEGER
title TEXT
department_id INTEGER
start_date TEXT
end_date TEXT

Sample rows

departments
id	name	location
1	Engineering	Berlin
2	Sales	London
3	People	New York

employees
id	name	email	department_id	manager_id	hire_date	title
1	Amira Hassan	amira@querylab.example	1	NULL	2021-03-01	Staff Engineer
2	Leo Martins	leo@querylab.example	1	1	2022-06-15	Software Engineer
3	Priya Nair	priya@querylab.example	1	1	2023-01-09	Software Engineer

salaries
id	employee_id	amount	effective_date
1	1	145000	2024-01-01
2	1	132000	2023-01-01
3	2	98000	2024-01-01

job_history
id	employee_id	title	department_id	start_date	end_date
1	2	Junior Engineer	1	2022-06-15	2023-12-31
2	3	Junior Engineer	1	2023-01-09	2024-03-31
3	5	SDR	2	2023-04-17	2024-06-30

Draft saves automatically

Ctrl or Cmd + Enter runs the query. The database resets on every run.

Run the query to see rows from the sample database.

Next exercise: Active MRR by plan