Fifth Highest Salary In A Department
Reported by candidates from ZS's online assessment. Pattern, common pitfall, and the honest play if you blank under the timer.
The ZS report from September 2026 comes down to one data structure: a ranked window over each department's distinct salaries. You're asked for the fifth-highest salary per department, and the interviewer then pushed on how to order the output when salaries tie. If your OA invite is sitting in your inbox, this is the query to rehearse tonight. It looks like a basic ranking question, but the dense-rank detail and the omit-small-departments rule trip people up. StealthCoder is the safety net if you blank on the live OA, but the pattern is short enough to own before then.
The problem
The table employees stores one row per employee, including that employee's department and salary. For each department, rank that department's distinct salary values from highest to lowest using dense ranks. Equal salaries share one rank. Return the salary whose dense rank is exactly 5. Omit any department that has fewer than five distinct salary values. Return department_id and fifth_highest_salary. Order rows by department_id ascending. What the interview report shared The interviewer asked SQL for the fifth-highest salary within a department, then asked how to order the result deterministically when salaries tie. employeesColumnMeaning employee_idUnique employee identifier employee_nameEmployee display name department_idDepartment identifier salaryEmployee salary Tables employees: employee_id PK (Integer), employee_name (Text), department_id (Integer), salary (Integer) Constraints The employees table has between 1 and 10^5 rows. employee_id is unique and non-null. employee_name, department_id, and salary are non-null. 0 <= salary <= 10^9.
Reported by candidates. Source: FastPrep
Pattern and pitfall
The trick is DENSE_RANK() OVER (PARTITION BY department_id ORDER BY salary DESC). Dense rank gives equal salaries one shared rank and leaves no gaps, which is exactly what the problem wants. Filter the ranked rows where rank = 5, then return department_id and salary as fifth_highest_salary. Departments with fewer than five distinct salaries never produce a rank 5 row, so they drop out on their own. No extra HAVING needed. The pitfall is duplicates. Several employees can share the fifth salary, so you'd return repeated rows. Use SELECT DISTINCT or group the ranked rows by department_id, salary. Then ORDER BY department_id ASC for deterministic output. Don't use ROW_NUMBER or RANK, since both break on ties. If you blank during the live OA, StealthCoder can supply the window function skeleton, but you should be able to write it from memory.
If you see this problem in your OA tomorrow, the play is to recognize the pattern in 30 seconds. StealthCoder buys you that recognition.
You can drill Fifth Highest Salary In A Department cold, or you can hedge it. StealthCoder runs invisibly during screen share and surfaces a working solution in under 2 seconds. The proctor sees the IDE. They don't see what's behind it. Built by an Amazon engineer who passed his OA cold and still thinks the filter is broken.
Get StealthCoderRelated leaked OAs
You've seen the question.
Make sure you actually pass ZS's OA.
ZS reuses patterns across OAs. Built by an Amazon engineer who passed his OA cold and still thinks the filter is broken. Works on HackerRank, CodeSignal, CoderPad, and Karat.
Fifth Highest Salary In A Department FAQ
What's the trick to the fifth highest salary per department?+
Use DENSE_RANK() partitioned by department_id and ordered by salary descending, then keep rows where the rank equals 5. Dense rank collapses tied salaries into one rank, so you get the fifth distinct salary value, not the fifth employee.
Why not ROW_NUMBER or RANK?+
ROW_NUMBER gives tied salaries different numbers, so you'd pick the wrong value. RANK leaves gaps after ties, so rank 5 might not exist even when five distinct salaries do. DENSE_RANK matches the 'distinct salary values' wording exactly.
How do I handle departments with fewer than five distinct salaries?+
You don't need special logic. If a department has fewer than five distinct salaries, no row gets dense rank 5, so the filter removes it. The department simply doesn't appear in the output, which is what the problem asks for.
How do I make the output deterministic when salaries tie?+
Multiple employees can share the rank 5 salary, producing duplicate rows. Apply SELECT DISTINCT on department_id and salary, or group by both. Then add ORDER BY department_id ASC. The interviewer's tie question was really about this dedupe and ordering step.
How do I prepare for this in 48 hours?+
Write the DENSE_RANK query from scratch three times, using a different N each time. Then test it on a small table with ties and a department that has only four salaries. Practice the subquery or CTE wrapper, since you can't filter on a window function directly in WHERE.