Highest Salary by Department

EasySQL · PostgreSQL00:00

Practice interviewer

In session

5 left

00:00

Your question is Highest Salary by Department. Start with the requirements and the two 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.

You need to log in / sign up to run or submit.

Problem

Caterpillar's workforce analytics team needs a department-level view of compensation. Write a PostgreSQL query that identifies the employee or employees with the highest non-null annual salary in every department represented in the employee data.

Requirements

Join employees to departments using department_id.
Return the department name, employee name, and highest annual salary.
Include every employee tied for the highest salary in a department.
Exclude employees whose salary is NULL, and sort the result by department name and employee name.

Schema

departments

Column	Type	Description
department_idPK	INTEGER	Unique department identifier
department_name	VARCHAR(100)	Caterpillar department name

employees

Column	Type	Description
employee_idPK	INTEGER	Unique employee identifier
employee_name	VARCHAR(100)	Employee full name
department_id	INTEGER	Department assigned to the employee
annual_salary	NUMERIC(12,2)	Annual salary in U.S. dollars

Tablesdepartmentsemployees

Your solutionPostgreSQL

You need to log in / sign up to run or submit.

Run a query to see results