Find the Highest-Paid Employee in Each Department
Department Highest Salary
Given the following PostgreSQL schema:
departments
----------
department_id BIGINT PRIMARY KEY
name TEXT NOT NULL
employees
---------
employee_id BIGINT PRIMARY KEY
name TEXT NOT NULL
salary NUMERIC NOT NULL
department_id BIGINT NOT NULL REFERENCES departments(department_id)
Write a single PostgreSQL query to find all employees whose salary is the highest within their respective department. The query should return the department name, employee name, and salary.
Requirements:
- Return columns:
department,employee,salary. - If multiple employees tie for the highest salary in a department, include all of them.
- Departments with no employees should not be included in the result.
- Assume department names and employee names are not necessarily unique.
- The order of the returned rows does not matter.
Similar LeetCode problem: Department Highest Salary
AI interview prep
Core dilemma
Efficiently identifying and aggregating the maximum value (salary) within distinct groups (departments) while handling ties, and then joining this aggregated information back to the original employee data.
3 tradeoffs and 2 stress tests locked
Interview prep
Unlock the interviewer X-Ray rubric, tradeoff matrix, stress tests, clarifying questions, and key concepts.
Find the Highest-Paid Employee in Each Department
Department Highest Salary
Given the following PostgreSQL schema:
departments
----------
department_id BIGINT PRIMARY KEY
name TEXT NOT NULL
employees
---------
employee_id BIGINT PRIMARY KEY
name TEXT NOT NULL
salary NUMERIC NOT NULL
department_id BIGINT NOT NULL REFERENCES departments(department_id)
Write a single PostgreSQL query to find all employees whose salary is the highest within their respective department. The query should return the department name, employee name, and salary.
Requirements:
- Return columns:
department,employee,salary. - If multiple employees tie for the highest salary in a department, include all of them.
- Departments with no employees should not be included in the result.
- Assume department names and employee names are not necessarily unique.
- The order of the returned rows does not matter.
Similar LeetCode problem: Department Highest Salary