🥈Second highest salary

“Second” is undefined without tie rules

Classic ranking puzzle: DISTINCT + OFFSET, DENSE_RANK, or ROW_NUMBER each answer a different question when ties or NULL salaries exist. Interview credit comes from naming assumptions, not from memorizing one dialect snippet.

SQL

DISTINCT + OFFSET (PostgreSQL)

SELECT DISTINCT salary
FROM employees
WHERE salary IS NOT NULL
ORDER BY salary DESC
OFFSET 1 LIMIT 1;

✓Illustrative result set

salary
118000

SQL

DENSE_RANK (handles ties for the top band)

SELECT salary
FROM (
  SELECT salary, DENSE_RANK() OVER (ORDER BY salary DESC) AS rnk
  FROM employees
  WHERE salary IS NOT NULL
) x
WHERE rnk = 2;

✓Illustrative result set

salary
118000

⚠️Edge cases

Say what you return before you code.

Case	Expected behavior (state yours)
<2 distinct salaries	Empty set or NULL, pick one and justify.
Many ties for #1	`DENSE_RANK = 2` returns the next distinct level; `OFFSET 1` may skip to an arbitrary row among ties, know the difference.

MAX-of-MAX tricks

Nested MAX queries that break when two people share the top salary.
LIMIT without ORDER BY.
Forgetting PARTITION BY when the prompt says “per department.”

Explicit ordering

Prefer windows for tie clarity; discuss index on (dept, salary DESC) when scale matters.
Test empty, single-row, and all-equal datasets out loud.

OFFSET cost

OFFSET-based answers scan early rows, fine in interviews; in production at scale you may prefer keyset or indexed window frames.

TL;DR

“Second highest salary” is solved by sorting distinct salaries descending and taking the second row, via DENSE_RANK()/ROW_NUMBER() windows, a subquery with LIMIT 1 OFFSET 1, or MAX excluding the top value. Interview answers differ from production: prefer windows with explicit tie rules; OFFSET on huge sets is linear in offset depth.