Max Salary Across Departments | Dataford Interview Questions

Context

Informatica QA teams may need salary extracts grouped by organizational department for validation and reporting. The result should preserve departments even when no employee has a valid salary record.

Task

Write a PostgreSQL query that returns the maximum salary for every department in the Informatica employee directory, along with each employee who earns that maximum salary.

Requirements

Return every department, including departments with no employees or only NULL salaries.
Calculate the maximum non-NULL salary within each department.
Return all employees tied for the departmental maximum.
Sort by department ID and employee ID.

Schema

departments

Column	Type	Description
department_idPK	INT	Unique department identifier
department_name	VARCHAR(100)	Department name

employees

Column	Type	Description
employee_idPK	INT	Unique employee identifier
employee_name	VARCHAR(100)	Employee full name
department_id	INT	Department assigned to the employee
salary	DECIMAL(12,2)	Annual employee salary

Your question is Max Salary Across Departments. Start with the requirements and the two tables in the Question tab.

Run and submit as often as you like. When you're ready, talk me through your approach or go straight to the code.

Problem