Top Salaries By Department
Reported by candidates from ZS's online assessment. Pattern, common pitfall, and the honest play if you blank under the timer.
The ZS OA reported in September 2026 looks like a hard SQL question, but it reduces to one idea: compare each employee's salary to their department's max. That's it. If you've got an OA invite and you're worried about ties, this is the version that punishes people who reach for LIMIT 1 or ROW_NUMBER. The prompt wants every employee tied at the top, sorted by department_id then employee_id. It's a short query once you see the shape. StealthCoder is the safety net if you blank on the live OA, running invisibly while you work.
The problem
The table employees stores one row per employee, including that employee's department and salary. For each department, find the highest salary. Return every employee who earns that department-highest salary, including all employees tied at the top. Return department_id, employee_id, employee_name, and salary. Order rows by department_id ascending, then employee_id ascending. What the interview report shared The interviewer asked SQL to find the top salaries by department. 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 separating two steps: find the max salary per department, then keep every employee who matches it. Two clean ways. First, a window function: SELECT MAX(salary) OVER (PARTITION BY department_id) as a column in a subquery, then filter where salary equals that value. Second, a join against a GROUP BY department_id subquery on both department_id and salary. The common pitfall is ROW_NUMBER, which breaks ties arbitrarily and drops people. RANK or DENSE_RANK with rank = 1 works too. Another miss is joining on salary alone, which pulls in employees from other departments who happen to earn the same amount. Join on department_id as well. Finish with ORDER BY department_id, employee_id. Nulls aren't a concern here since the columns are non-null. If you freeze during the live OA, StealthCoder can surface the query while you keep your head clear.
If this hits your live OA and you blank, StealthCoder solves it in seconds, invisible to the proctor.
You can drill Top Salaries By 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 would have shipped this the night before his JPMorgan OA if he'd had it.
Get StealthCoderRelated leaked OAs
This OA pattern shows up on LeetCode as department highest salary. If you have time before the OA, drill that.
You've seen the question.
Make sure you actually pass ZS's OA.
ZS reuses patterns across OAs. Built by an Amazon engineer who would have shipped this the night before his JPMorgan OA if he'd had it. Works on HackerRank, CodeSignal, CoderPad, and Karat.
Top Salaries By Department FAQ
How hard is the ZS Top Salaries By Department question really?+
Easy to medium. The logic is one comparison: salary equals the department max. Most mistakes come from tie handling and from ordering. If you know window functions or a grouped subquery join, you can write it in a few minutes.
What's the trick to handling ties?+
Don't pick a single row per department. Compute the department max, then return every row matching it. Use MAX() OVER (PARTITION BY department_id), or RANK() = 1. Avoid ROW_NUMBER and LIMIT, since they cut tied employees out.
Should I use a window function or a join?+
Either is fine. The window version is shorter: compute MAX(salary) OVER (PARTITION BY department_id) in a subquery and filter. The join version groups by department_id and joins back on department_id and salary. Pick the one you can write without second-guessing.
What ordering does the output need?+
Order by department_id ascending, then employee_id ascending. Put ORDER BY department_id, employee_id at the very end of the outer query. Forgetting the second sort key is an easy way to fail a test case when ties exist.
How do I prepare for this in 48 hours?+
Write this query three ways: window MAX, RANK = 1, and grouped subquery join. Then test a case with tied top salaries and a case with a single-employee department. Also rehearse the join-on-both-columns mistake so you spot it fast.