Generated by Codex with GPT-5

Quick facts

What the problem is really asking

There is an Employee table with a salary column. The task is to return the second-highest distinct salary value.

That word “distinct” is the whole trick.

If the largest salary appears many times, it still counts as only one rank. So this is not:

  • the second row after sorting
  • the second employee record
  • the salary from the person with the second-largest id

It is simply the largest salary that is strictly smaller than the overall maximum salary. If no such value exists, the answer should be NULL.

The key idea

Once the problem is stated that way, the cleanest SQL logic becomes obvious:

  1. find the highest salary in the table
  2. ignore every row with that salary
  3. take the maximum of what remains

In one sentence, the answer is:

the maximum salary among rows whose salary is less than the global maximum salary

That is why the classic solution is so short. It is just the definition of “second-highest distinct salary” translated directly into SQL.

How to derive the optimal solution

A useful way to derive it is to test the definition on small examples.

If the salaries are [100, 200, 300], the answer is 200.

If the salaries are [100, 300, 300], the answer is still 100, because the top rank is still just 300.

If the salaries are [300, 300], there is no second distinct salary, so the answer should be NULL.

That reasoning naturally leads to one condition:

look only at salaries that are strictly below the maximum salary

Once that filter is in place, taking MAX(salary) from the remaining rows gives exactly the second-highest distinct value.

The nice part is the empty-table behavior. If no row survives the filter, SQL’s MAX(...) over an empty result returns NULL, which is exactly what the problem asks for.

Best solution to remember

The most practical answer to remember is the aggregate-based query:

SELECT MAX(salary) AS SecondHighestSalary FROM Employee WHERE salary < (SELECT MAX(salary) FROM Employee);

It is easy to explain, easy to verify against duplicates, and directly matches the definition of the answer.

Another valid family of solutions uses DISTINCT plus ordering or a window function such as DENSE_RANK(). Those are useful generalizations, but for this exact question the MAX-with-filter version is usually the cleanest final answer.

Why this works

The inner MAX(salary) computes the top distinct salary value in the table.

The WHERE salary < ... predicate removes every row at that top value, including duplicates of the maximum salary. That automatically handles the “distinct” requirement without needing extra logic.

Then the outer MAX(salary) asks for the largest salary left. By definition, that is the second-highest distinct salary.

If nothing is left, the outer aggregate returns NULL, so the edge case is covered naturally.

Complexity

In interview terms, the core business rule is linear: track the largest distinct value and the best value below it.

In database terms, the exact runtime depends on the query planner and indexing. Without a helpful index, the engine may need full scans. With an index on salary, finding the top values can be cheaper. For this problem, the important point is usually not micro-optimizing the engine plan, but choosing a query whose logic is obviously correct.

Python solution

The real LeetCode submission is SQL, but the Python below expresses the same rule in application code: keep the largest distinct salary seen so far and the best salary strictly below it.

from dataclasses import dataclass
from typing import Iterable, Optional


@dataclass(frozen=True, slots=True)
class EmployeeRecord:
    employee_id: int
    salary: int


class TopTwoDistinctSalaries:
    """Track the two largest distinct salary values seen so far."""

    def __init__(self) -> None:
        self.highest: Optional[int] = None
        self.second_highest: Optional[int] = None

    def consider(self, salary: int) -> None:
        """Update the tracked values with one salary observation."""
        if self.highest is not None and salary == self.highest:
            # Duplicate copies of the top salary do not create a new distinct rank.
            return

        if self.highest is None or salary > self.highest:
            self.second_highest = self.highest
            self.highest = salary
            return

        if self.second_highest is not None and salary == self.second_highest:
            return

        if self.second_highest is None or salary > self.second_highest:
            self.second_highest = salary


def find_second_highest_distinct_salary(
    employees: Iterable[EmployeeRecord],
) -> Optional[int]:
    """Return the second-highest distinct salary, or None if it does not exist."""
    salary_tracker = TopTwoDistinctSalaries()

    for employee in employees:
        salary_tracker.consider(employee.salary)

    return salary_tracker.second_highest

This runs in O(n) time with O(1) extra space. It mirrors the same idea as the SQL answer: ignore duplicates of the top value, and remember the best remaining distinct candidate.

Interview follow-ups

How would you solve it with a window function?

Use DENSE_RANK() over salaries ordered from highest to lowest, then return the salary whose dense rank is 2. This works because dense ranking assigns the same rank to duplicate salaries, which is exactly what the problem means by distinct salary values.

The tradeoff is that this is more machinery than necessary for the base problem. It is still a very good answer, especially in modern SQL, but it is usually less minimal than the aggregate-based solution.

What if the interviewer changes the question to the nth highest distinct salary?

Then the window-function approach becomes more attractive. DENSE_RANK() generalizes naturally because the nth highest distinct salary is just the row or set of rows with dense rank n. Another common approach is SELECT DISTINCT salary, ordered descending, with an offset of n - 1.

This works because ranking or offsetting over distinct salary values matches the meaning of “nth distinct salary.” The tradeoff is that these solutions are more general but often more verbose than the specialized second-highest query.

What if duplicate salaries should count as separate rows instead of separate values?

Then the problem definition changes. Now the second answer after sorting could still be equal to the highest salary if the maximum appears twice. In SQL terms, that means DISTINCT or DENSE_RANK() is no longer the right mental model. A row-based ranking such as ROW_NUMBER() over descending salary would be closer to the new requirement.

This matters because the original problem is about distinct values, not record positions. A candidate who can explain that difference clearly usually understands the problem much more deeply than someone who only memorized one query.

What if the table is very large and salary is indexed?

An index on salary can make top-value lookups and ordered access much cheaper than a full table sort. That can help both the aggregate-based solution and ranking-based solutions, depending on the database engine and execution plan.

The tradeoff is that interview SQL is usually judged first on correctness and clarity, not on vendor-specific planner behavior. So the right move is to state the clean query first, then mention that indexing on salary can improve performance on large tables.

Practical takeaway

The fastest way to understand this problem is to stop thinking about employees and start thinking about value ranks.

The second-highest distinct salary is just the largest value below the maximum. Once that framing is clear, the SQL answer is almost impossible to forget.