Highest-Earning Employees By Department
Reported by candidates from JP Morgan's online assessment. Pattern, common pitfall, and the honest play if you blank under the timer.
This JP Morgan SQL question, reported in September 2026, looks like a ranking problem but it's really a max-per-group filter with ties. If you've got the OA coming up, read this twice: the whole task is "keep rows whose salary equals their department's top salary." That's it. The ordering clause and the tie rule are where people lose points. StealthCoder sits invisibly on your screen as a safety net if you blank mid-assessment, but this one is short enough to own before you start.
The problem
The table employees stores one row per employee, including that employee's department and salary. For each department, find its 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 interview report listed a SQL task about the highest-earning employee at the department level. 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 comparing each row to its department's max. Two clean ways. First, a window function: compute MAX(salary) OVER (PARTITION BY department_id) in a subquery, then filter where salary equals that value. Second, join employees to a grouped subquery of department_id and MAX(salary) on both department_id and salary. The common pitfall is using LIMIT 1 or ROW_NUMBER, which drops tied employees. The prompt says include everyone tied, so RANK or DENSE_RANK = 1 also works, but MAX OVER is simpler. Another pitfall is grouping by department and selecting employee_name, which breaks in strict SQL. Don't forget ORDER BY department_id, employee_id, both ascending. Salary and names are non-null here, so NULL handling isn't a concern. If you freeze during the live OA, StealthCoder can hand you the query so you can check it against your own logic.
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 Highest-Earning Employees 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 passed his OA cold and still thinks the filter is broken.
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 JP Morgan's OA.
JP Morgan 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.
Highest-Earning Employees By Department FAQ
How hard is the JP Morgan highest-earning employees SQL question really?+
Easy to medium. It's one filter on a per-department maximum. If you know window functions or a grouped subquery join, it takes a few minutes. The only real risk is mishandling ties or forgetting the two-column ORDER BY.
What's the trick to handling ties?+
Don't pick one row per department. Compute the department max, then keep every row whose salary equals it. MAX(salary) OVER (PARTITION BY department_id) or RANK() = 1 both keep all tied employees. ROW_NUMBER and LIMIT drop them.
Should I use a window function or a join?+
Either passes. A window function is shorter: subquery with MAX OVER partition, then WHERE salary = dept_max. The join version groups by department_id for the max and joins back on department and salary. Use whichever you can write without bugs.
Can I use GROUP BY alone?+
No. GROUP BY department_id gives you the max salary but not the employees who earn it. Selecting employee_id or employee_name alongside it either errors or returns arbitrary values. You need a join back or a window function.
How do I prepare for this in 48 hours?+
Write the query three ways: MAX OVER, RANK = 1, and a grouped join. Test each on a small table with a tie and a single-employee department. Then practice the ORDER BY department_id, employee_id ending so it's automatic.