Second Highest Salary — Solution & ExplanationProblems / Second Highest Salary / EditorialApproach

Overall maximum: (SELECT MAX(salary) FROM employees).

Second-highest distinct: the maximum among salaries *strictly below* it — SELECT MAX(salary) FROM employees WHERE salary < (SELECT MAX(salary) FROM employees). Because the inner comparison is against the max *value*, multiple employees tied at the top don't matter: everything at the top level is excluded, and MAX of the remainder is the next distinct level.

Filter: keep employees whose salary equals that value; ties at the second level all survive.

Order by employee_id.

The PySpark version makes the "distinct levels" idea explicit: select("salary").distinct(), sort descending, limit(2), then MIN of those two rows is the second level. A one-row crossJoin broadcasts it back onto every employee for the final filter.Key InsightORDER BY salary DESC LIMIT 1 OFFSET 1 is the tempting shortcut, and it fails twice here: without DISTINCT the offset lands on a duplicate of the top salary, and with LIMIT 1 it returns a single employee even when several tie at the second level. The max-below-max subquery handles both tie scenarios by construction — which is exactly why interviewers seed the data with ties.Hints

Reframe it: the second-highest salary is the highest salary among those strictly below the overall maximum.

That is a nested scalar subquery: SELECT MAX(salary) FROM employees WHERE salary < (SELECT MAX(salary) FROM employees).

Compare with < against the max, not against a specific employee — the strict inequality is what makes top-level ties collapse into one salary level.

In PySpark: take distinct salaries, sort descending, limit(2), and the MIN of those two rows is the second-highest — crossJoin it back and filter.

Solve Second Highest Salary yourself →