MediumHR18 min

Above-average current salaries

Challenge

List employees whose latest salary is above the average latest salary.

Business context

Compensation is reviewing the current band, not historical offers. Each employee has one or more salary rows.

Instructions

Use the latest effective_date per employee as the current salary. Return name and annual_salary for people above the average of those current salaries. 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: Invoiced amount by workspace