HardHR16 min

Current salary for each employee

Challenge

Return one current salary row per employee.

Business context

Payroll history keeps old salaries. The board pack should show the contract in force, one row per person.

Instructions

Return name, title, and annual_salary. Choose the salary with the latest effective_date for each employee. Sort by annual_salary descending, then 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: Rank products by revenue