Employees Above Average Salary
Reported by candidates from JP Morgan's online assessment. Pattern, common pitfall, and the honest play if you blank under the timer.
The detail that matters here is the word "strictly." JP Morgan's September 2026 SQL report asks for every employee whose salary is above the overall average, and an employee sitting exactly at the average doesn't make the cut. It's a short query, but the tie-breaking order and the comparison operator are where people lose points. You need employee_id, employee_name and salary, sorted by salary descending, then employee_id ascending. If you freeze on the OA, StealthCoder runs invisibly on your screen and hands you the query. You probably won't need it for this one.
The problem
The table employees stores one row per employee and their salary. Compute the average salary across all rows. Return every employee whose salary is strictly greater than that overall average. Return employee_id, employee_name, and salary. Order rows by salary descending, then employee_id ascending. What the interview report shared The interview report listed a SQL task about employees whose salary is greater than the average salary. employeesColumnMeaning employee_idUnique employee identifier employee_nameEmployee display name salaryEmployee salary Tables employees: employee_id PK (Integer), employee_name (Text), salary (Integer) Constraints The employees table has between 1 and 10^5 rows. employee_id is unique and non-null. employee_name and salary are non-null. 0 <= salary <= 10^9.
Reported by candidates. Source: FastPrep
Pattern and pitfall
The trick is a scalar subquery. Compute AVG(salary) once, then compare each row against it: SELECT employee_id, employee_name, salary FROM employees WHERE salary > (SELECT AVG(salary) FROM employees) ORDER BY salary DESC, employee_id ASC. A window function, AVG(salary) OVER (), also works but needs a wrapping subquery because you can't filter on a window function in WHERE. The common pitfalls are using >= instead of >, forgetting the second sort key, and joining the table to itself for no reason. Salary is non-null, so NULL handling isn't an issue, and integer salaries up to 10^9 are fine because AVG returns a decimal in most engines. Watch for integer division only if you hand-roll SUM/COUNT. If you blank on the live OA, StealthCoder is the safety net that surfaces the subquery pattern while you stay in control of what you submit.
Memorize the pattern. If you can't, run StealthCoder. The proctor sees the IDE. They don't see what's behind it.
You can drill Employees Above Average Salary 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. Made by an engineer who treats the OA as theater. If yours is tonight, you don't have time to grind. You have time to hedge.
Get StealthCoderRelated leaked OAs
You've seen the question.
Make sure you actually pass JP Morgan's OA.
JP Morgan reuses patterns across OAs. Made by an engineer who treats the OA as theater. If yours is tonight, you don't have time to grind. You have time to hedge. Works on HackerRank, CodeSignal, CoderPad, and Karat.
Employees Above Average Salary FAQ
How hard is the JP Morgan employees above average salary question really?+
It's easy. One scalar subquery, one WHERE filter, one ORDER BY. The only real risks are using >= instead of > and missing the secondary sort on employee_id. If you've written a subquery before, you can finish this in a couple of minutes.
What's the trick to this query?+
Compute the average once in a subquery and compare each row to it. Write WHERE salary > (SELECT AVG(salary) FROM employees). You don't need a join or a GROUP BY, because the average is a single value across the whole table.
Can I use a window function instead of a subquery?+
Yes, but not directly in WHERE. Compute AVG(salary) OVER () in an inner query or CTE, then filter on it in the outer query. It works fine, but the plain scalar subquery is shorter and harder to get wrong under time pressure.
Does the sort order matter in this problem?+
Yes. The task says salary descending, then employee_id ascending. Skipping the tie-breaker can fail the test cases when several employees share a salary. Write ORDER BY salary DESC, employee_id ASC and you're covered.
How do I prepare for this in 48 hours?+
Practice five or six aggregate-comparison queries: above average, above department average, top N per group. Focus on scalar subqueries, CTEs and ORDER BY with tie-breakers. Re-read the problem statement for words like strictly, distinct and ordering. Those words decide most SQL failures.