What mistakes do candidates make on this question?

Only provides one approach — interviewers expect you to know multiple methods No mention of edge cases: what if there are duplicate salaries? What if the table is empty? Missing window functions (DENSE_RANK, ROW_NUMBER) — the modern SQL standard approach No discussion of NULL handling or performance implications Doesn't demonstrate ability to think through trade-offs between approaches

mediumSQLAnswer Breakdown

Second Highest Salary SQL Query — Weak vs Strong Answer Breakdown

Q: What is the best way to answer "Write an SQL query to find the second-highest salary from an employee table."?

The difference between a junior and senior answer: juniors give ONE working query. Seniors demonstrate multiple approaches, explain trade-offs (duplicates, NULLs, performance), and show they can generalize the pattern.

See exactly why most candidates fail the second-highest salary SQL question. Compare a weak answer with a FAANG-level response that gets offers.

Original Interview Question

Write an SQL query to find the second-highest salary from an employee table.

View Full Question

✗

The Weak Answer (What Most Candidates Say)

You can use a subquery to find the second highest salary:

SELECT MAX(salary) FROM employee WHERE salary < (SELECT MAX(salary) FROM employee);

This finds the maximum salary that is less than the overall maximum, giving us the second highest.

⚠

Why This Answer Fails

1.Only provides one approach — interviewers expect you to know multiple methods
2.No mention of edge cases: what if there are duplicate salaries? What if the table is empty?
3.Missing window functions (DENSE_RANK, ROW_NUMBER) — the modern SQL standard approach
4.No discussion of NULL handling or performance implications
5.Doesn't demonstrate ability to think through trade-offs between approaches

✓

The FAANG-Level Answer

There are three standard approaches, each with different trade-offs:

Approach 1 — Subquery with MAX (Basic):

sql
SELECT MAX(salary) AS second_highest
FROM employee
WHERE salary < (SELECT MAX(salary) FROM employee);

Simple, but scans the table twice and fails silently when duplicates exist.

Approach 2 — DENSE_RANK (Preferred):

sql
SELECT salary AS second_highest
FROM (
  SELECT salary, DENSE_RANK() OVER (ORDER BY salary DESC) AS rnk
  FROM employee
) ranked
WHERE rnk = 2;

Handles duplicates correctly — DENSE_RANK assigns the same rank to tied salaries without gaps. Single pass.

Approach 3 — OFFSET/LIMIT with DISTINCT:

sql
SELECT DISTINCT salary
FROM employee
ORDER BY salary DESC
LIMIT 1 OFFSET 1;

Concise for MySQL/PostgreSQL. Not ANSI-standard.

Edge cases I'd handle in production:

If fewer than 2 distinct salaries exist, return NULL (wrap in a CTE with a count check)
For Nth salary generalization, parameterize the rank value
Index on salary column makes all approaches O(log n) instead of O(n)

Key Takeaway

The difference between a junior and senior answer: juniors give ONE working query. Seniors demonstrate multiple approaches, explain trade-offs (duplicates, NULLs, performance), and show they can generalize the pattern.

Want to know if YOUR answer is weak or strong?

Paste your answer and get instant AI-powered feedback with a FAANG-level improved version.

Analyze My Answer — Free

3 free analyses per day. No sign-up required.

Second Highest Salary SQL Query — Weak vs Strong Answer Breakdown

See exactly why most candidates fail the second-highest salary SQL question. Compare a weak answer with a FAANG-level response that gets offers.

Original Interview Question

Write an SQL query to find the second-highest salary from an employee table.

View Full Question

✗

The Weak Answer (What Most Candidates Say)

You can use a subquery to find the second highest salary:

SELECT MAX(salary) FROM employee WHERE salary < (SELECT MAX(salary) FROM employee);

This finds the maximum salary that is less than the overall maximum, giving us the second highest.

⚠

Why This Answer Fails

1.Only provides one approach — interviewers expect you to know multiple methods
2.No mention of edge cases: what if there are duplicate salaries? What if the table is empty?
3.Missing window functions (DENSE_RANK, ROW_NUMBER) — the modern SQL standard approach
4.No discussion of NULL handling or performance implications
5.Doesn't demonstrate ability to think through trade-offs between approaches

✓

The FAANG-Level Answer

There are three standard approaches, each with different trade-offs:

Approach 1 — Subquery with MAX (Basic):

sql
SELECT MAX(salary) AS second_highest
FROM employee
WHERE salary < (SELECT MAX(salary) FROM employee);

Simple, but scans the table twice and fails silently when duplicates exist.

Approach 2 — DENSE_RANK (Preferred):

sql
SELECT salary AS second_highest
FROM (
  SELECT salary, DENSE_RANK() OVER (ORDER BY salary DESC) AS rnk
  FROM employee
) ranked
WHERE rnk = 2;

Handles duplicates correctly — DENSE_RANK assigns the same rank to tied salaries without gaps. Single pass.

Approach 3 — OFFSET/LIMIT with DISTINCT:

sql
SELECT DISTINCT salary
FROM employee
ORDER BY salary DESC
LIMIT 1 OFFSET 1;

Concise for MySQL/PostgreSQL. Not ANSI-standard.

Edge cases I'd handle in production:

If fewer than 2 distinct salaries exist, return NULL (wrap in a CTE with a count check)
For Nth salary generalization, parameterize the rank value
Index on salary column makes all approaches O(log n) instead of O(n)

Key Takeaway

Want to know if YOUR answer is weak or strong?

Paste your answer and get instant AI-powered feedback with a FAANG-level improved version.

Analyze My Answer — Free

3 free analyses per day. No sign-up required.

Second Highest Salary SQL Query — Weak vs Strong Answer Breakdown

The Weak Answer (What Most Candidates Say)

Why This Answer Fails

The FAANG-Level Answer

Approach 1 — Subquery with MAX (Basic):

Approach 2 — DENSE_RANK (Preferred):

Approach 3 — OFFSET/LIMIT with DISTINCT:

Edge cases I'd handle in production:

Want to know if YOUR answer is weak or strong?

Related Interview Questions

Second Highest Salary SQL Query — Weak vs Strong Answer Breakdown

The Weak Answer (What Most Candidates Say)

Why This Answer Fails

The FAANG-Level Answer

Approach 1 — Subquery with MAX (Basic):

Approach 2 — DENSE_RANK (Preferred):

Approach 3 — OFFSET/LIMIT with DISTINCT:

Edge cases I'd handle in production:

Want to know if YOUR answer is weak or strong?

Related Interview Questions