Second highest salary

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

Sometimes you need the runner-up, not the winner. Find the second highest salary in the employees table. Return 1 column called second_highest_salary. When several people share the highest salary, still take the next lower different value. In other words, work with distinct salaries. Ignore people with no salary.

Requirements

• Return 1 row and 1 column, second_highest_salary.
• Look at distinct salary values, so two people with the same salary count as one.
• Ignore NULL salaries.
• The answer is the second largest of those distinct values.

Constraints

• The table is employees.
• Use DISTINCT, sort from high to low, and skip the first row with OFFSET 1.
• If you want to be safe, wrap the logic so that a missing second value returns NULL, not an error.

Examples

Input: employees name | salary Alice Chen | 120000 Dan Park | 110000 Omar Ali | 105000 Bob Kumar | 95000 Eve Ng | 95000 Output: second_highest_salary | 110000 | Why this passes: Distinct ordered salaries: 120000, 110000, 105000, 95000. Second is 110000.

Topics: lakebench, sql, nth, salary.

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