Highest and lowest salary per department

SQL data engineering interview problem. Difficulty: intermediate. Pattern: Window Functions. About 12 minutes. Part of the Pro drill bank.

For each department, a report wants to show who earns the most and who earns the least, as two names in 1 row. For every department that is not NULL, return 1 row with department, highest_paid (the name of the employee with the highest salary) and lowest_paid (the name of the employee with the lowest salary). Use window functions and ignore employees with a NULL salary. When salaries tie, pick the name that comes first alphabetically. Sort by department.

Requirements

• Return 1 row for each department.
• highest_paid is the name with the highest salary in that department.
• lowest_paid is the name with the lowest salary in that department.
• Ignore NULL salaries and NULL departments.
• For ties, choose the alphabetically first name.

Constraints

• The table is employees.
• Use FIRST_VALUE(name) OVER (PARTITION BY department ORDER BY salary DESC, name) for the highest, and the same with ASC for the lowest.
• Because a window function returns 1 row for each employee, use SELECT DISTINCT or a group to keep 1 row for each department.

Examples

Input: employees (Sales) name | department | salary Omar Ali | Sales | 105000 Max Reed | Sales | NULL Output: department | highest_paid | lowest_paid Sales | Omar Ali | Omar Ali Why this passes: Only Omar has a non-null Sales salary, so highest and lowest are the same person. Max is excluded by the NULL filter.

Topics: lakebench, sql, first_value, last_value.

More SQL interview questions · All interview problems · Learn data engineering