Amazon·Software Engineer·Technical Phone Screen·Senior

Senior

Jun 2026

Summary

SQL question for an Applied Scientist role at Amazon. Pretty standard stuff but worth knowing cold if you're interviewing there.

Questions Asked (1)

Write a SQL query to return the second highest distinct salary from an Employee table. If there is no second highest salary, the query should return null.

Algorithms & Data StructuresData Modeling

Author's notes

Classic question but the null-return requirement is where people slip up.

Create a free account to read the full note

AI HintsAI Generated

Suggested Approach

Use a subquery with the DISTINCT keyword to get unique salaries, order them in descending order, and use LIMIT/OFFSET to pick the second one. Wrap the query so that if no second salary exists, it returns NULL. Alternatively, use a window function like DENSE_RANK() to rank distinct salaries and filter for rank = 2.

Pro tip: Always clarify the SQL dialect (e.g., MySQL, PostgreSQL) because LIMIT/OFFSET syntax varies, and mention that you'd test edge cases like all employees having the same salary or fewer than two distinct salaries.

1. Clarify requirements and edge cases

Confirm that 'second highest distinct salary' means the second unique salary value, and that if it doesn't exist, the result should be NULL. Ask about the SQL dialect if not specified.

2. Choose an approach

Decide between using a subquery with LIMIT/OFFSET or a window function like DENSE_RANK(). The subquery approach is simpler but may not be portable; the window function is more standard and handles ties.

3. Write the query

For the subquery approach: SELECT (SELECT DISTINCT salary FROM Employee ORDER BY salary DESC LIMIT 1 OFFSET 1) AS SecondHighestSalary. For the window function approach: SELECT MAX(salary) FROM (SELECT salary, DENSE_RANK() OVER (ORDER BY salary DESC) as rnk FROM Employee) t WHERE rnk = 2.

4. Handle NULL case

Ensure the query returns NULL when there is no second highest salary. The subquery approach naturally returns NULL if no rows; the window function approach may need a COALESCE or a check.

5. Test with edge cases

Mentally test with: empty table, one distinct salary, multiple employees with the same salary, and multiple distinct salaries. Verify the query returns the correct result in each case.

Key Points to Mention

Use of DISTINCT to ensure salaries are unique before ranking.
ORDER BY salary DESC to sort from highest to lowest.
LIMIT and OFFSET (or equivalent) to skip the highest and pick the second.
Window functions like DENSE_RANK() as an alternative for handling ties and portability.
Handling the NULL case when no second highest salary exists.
Performance considerations: indexing on salary column can speed up sorting.

AI-generated suggestions, not part of the candidate's original notes. May be inaccurate — verify before relying on them.