Skip to content
LakeBench
ProblemsCommunityPricing
Sign inStart practicing

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 non-null department, return one row with: department highest_paid: name of the employee with the highest salary lowest_paid: name of the employee with the lowest salary Use window functions. Exclude NULL salaries. Order by department.

Requirements

  • One row per department.

Constraints

  • If salaries tie, pick the lexicographically smallest name.

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

intermediate

Highest and lowest salary per department

Interview-style drill: FIRST_VALUE / LAST_VALUE for top and bottom earner names per department.

For each non-null department, return one row with: - `department` - `highest_paid`: name of the employee with the highest salary - `lowest_paid`: name of the employee with the lowest salary Use window functions. Exclude NULL salaries. Order by `department`.