SQL data engineering interview problem. Difficulty: intermediate. Pattern: Window Functions. About 12 minutes. Part of the Pro drill bank.
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.
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
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`.