medium Departments with High Earning Employees

The problem

Given an Employee table with id, name, department, and salary, find every department that has at least two employees who earn more than that department's own average salary. Return the qualifying departments ordered by department name.

The phrase that carries all the weight is "more than that department's own average." The threshold is not a single global number — it is recomputed per department. An employee is a "high earner" only relative to the colleagues sitting in the same department, and a department qualifies only when two or more of its members clear their local bar.

The example data

We will trace the whole solution on this concrete table, so it helps to name the rows up front. Three departments, ten employees:

Note the three salaries in department A specifically: John 100, Dan 120, James 110. We will need all three again in a moment, and the fact that James earns 110 turns out to be the whole point.

Worked trace, department by department

Compute each department's average, then count how many of its members strictly exceed it.

Department A — avg = (100 + 120 + 110) / 3 = 110

• John 100 — below 110.
• Dan 120 — above 110. High earner.
• James 110 — equals 110. The condition is strictly greater than (>), so James does not count.

Department A has 1 high earner. Not enough.

Department B — avg = (100 + 130 + 115 + 125) / 4 = 117.5

• Amy 100 — below.
• Anne 130 — above. High earner.
• Ron 115 — below 117.5.
• Bob 125 — above. High earner.

Department B has 2 high earners. It qualifies.

Department C — avg = (90 + 95 + 100) / 3 = 95

• Kim 90 — below.
• Lee 95 — equals, not above.
• Sam 100 — above. High earner.

Department C has 1 high earner. Not enough.

Only department B clears the bar twice, so the answer is B.

Why a naive shortcut is wrong

A tempting simplification is to skip the per-department averages and compare every salary against one global average across the whole company. Let us actually run that wrong query on this data and watch it fail.

The global average over all ten salaries is

(100 + 120 + 110 + 100 + 130 + 115 + 125 + 90 + 95 + 100) / 10 = 1085 / 10 = 108.5

Now count, in each department, how many salaries exceed 108.5:

• Department A — recall A's salaries are John 100, Dan 120, James 110. Both Dan (120 > 108.5) and James (110 > 108.5) clear the global bar. That is 2 employees above 108.5, so the naive query treats A as qualifying.
• Department B — Anne (130) and Bob (125) are both above 108.5. That is 2, so B also qualifies.
• Department C — the highest is Sam at 100, which is below 108.5. 0 qualify.

So the global-average query returns {A, B} — it reports A as well. But the correct answer is {B} alone: against A's own average of 110, only Dan clears the bar, so A has just one genuine high earner. The naive query produces a false positive on A.

The reason is structural, not accidental. James earns 110 — above the company-wide 108.5 but below his own department's 110 — so a global threshold counts him while a per-department threshold correctly does not. That single employee flips A from "1 high earner" to "2," which is exactly the boundary the problem cares about. The lesson: when the spec says "above the department's average," the average must be recomputed inside each group; substituting a single global number is not an approximation, it changes the result set.

The query

The standard approach is a self-join: pair each employee with every colleague in the same department, average those colleagues' salaries to get the department average, and keep the employee only when their salary strictly exceeds it. Then count survivors per department and keep departments with two or more.

SELECT department
FROM (
    SELECT department, COUNT(*) AS high_earners
    FROM (
        SELECT e.id, e.name, e.department, e.salary,
               AVG(e2.salary) AS avg_salary
        FROM Employee e
        JOIN Employee e2 ON e.department = e2.department
        GROUP BY e.id, e.name, e.department, e.salary
        HAVING e.salary > AVG(e2.salary)
    ) AS high_earners_per_dept
    GROUP BY department
    HAVING COUNT(*) >= 2
) AS result
ORDER BY department;

• Innermost query: the self-join on e.department = e2.department gives, for each employee e, the set of all colleagues e2 in the same department; AVG(e2.salary) is therefore that department's average. The HAVING e.salary > AVG(e2.salary) keeps only genuine high earners — strictly greater, which is why James (110 = 110) and Lee (95 = 95) are excluded.
• Middle query: COUNT(*) per department, then HAVING COUNT(*) >= 2 keeps only departments with two or more high earners. Here only B survives.
• Outer query: projects the department name and applies ORDER BY department.

An equivalent and often clearer formulation uses a window function: AVG(salary) OVER (PARTITION BY department) computes the per-department average alongside each row without a self-join, after which you filter and count the same way.

Source

Problem and example data adapted from the LeetCode-style exercise "Departments with High Earning Employees" (Medium). Salary figures, per-department averages (A = 110, B = 117.50, C = 95.00), the global average (108.5), and the expected output (department B) were re-derived and verified directly from the example table in this lesson.