Second highest 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, you want the people who earn the second highest salary in that department. This is a common interview question because it combines ranking and groups. For each department that is not NULL, find the employees whose salary has a DENSE_RANK of 2 within the department. Return department, name and salary. Sort by department, then by salary from the highest, then by name. Ignore employees with a NULL department or a NULL salary.

Requirements

  • Rank salaries inside each department with DENSE_RANK, from highest to lowest.
  • Return the employees whose rank is exactly 2.
  • Ignore rows with a NULL department or NULL salary.
  • Sort by department, salary descending, then name.

Constraints

  • The table is employees.
  • Use DENSE_RANK() OVER (PARTITION BY department ORDER BY salary DESC).
  • Return only these 3 columns, in this order: department, name, salary.

Examples

Input: employees (Engineering slice) name | department | salary Alice Chen | Engineering | 120000 Dan Park | Engineering | 110000 Bob Kumar | Engineering | 95000 Output: department | name | salary Engineering | Dan Park | 110000 Why this passes: DENSE_RANK puts Alice at 1 and Dan at 2.

Topics: lakebench, sql, dense_rank, partition.

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

intermediate

Second highest salary per department

Interview-style drill: Per department, return the employee(s) with the 2nd highest distinct salary rank.

For each non-null department, find employees with the 2nd highest salary using DENSE_RANK. Return `department`, `name`, `salary` where dense rank = 2. Order by `department`, then `salary` descending, then `name`.