medium Departments with High Earning Employees

Problem Statement

Table: Employee
Each row in this table represents an individual employee, detailing their unique ID, name, department, and salary.

+-------------+---------+
| Column Name | Type    | 
+-------------+---------+
| id          | int     | 
| name        | varchar |
| department  | varchar |
| salary      | int     |
+-------------+---------+
id is the primary key for this table.

Develop a solution to find departments with at least two employees who earn more than their department's average salary.

Return the result table in order of department.

Example

Input:

Employee table:
+-----+-------+------------+--------+
| id  | name  | department | salary |
+-----+-------+------------+--------+
| 101 | John  | A          | 100    |
| 102 | Dan   | A          | 120    |
| 103 | James | A          | 110    |
| 104 | Amy   | B          | 100    |
| 105 | Anne  | B          | 130    |
| 106 | Ron   | B          | 115    |
| 107 | Bob   | B          | 125    |
| 108 | Kim   | C          | 90     |
| 109 | Lee   | C          | 95     |
| 110 | Sam   | C          | 100    |
+-----+-------+------------+--------+

Output:

+------------+
| department |
+------------+
| B          |
+------------+

In this example, department B has at least two employees who earn more than the average salary of their department.

Try It Yourself

Solution

To identify departments with at least two employees earning above their department's average salary, we can follow a structured approach that involves calculating averages, filtering high earners, and aggregating the results accordingly.

Approach Overview

• Calculate Department Average Salary: Determine the average salary for each department.
• Identify High Earners: Find employees whose salaries exceed their department's average.
• Aggregate High Earners by Department: Count the number of high earners in each department.
• Select Eligible Departments: Retrieve departments that have at least two high earners.
• Order the Results: Sort the final output by department name.

SQL Query

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 subquery
    GROUP BY department
    HAVING COUNT(*) >= 2
) AS result
ORDER BY department;

Step-by-Step Approach

Step 1: Calculate Department Average Salary

Compute the average salary for each department to establish a benchmark for identifying high earners.

SQL Query:

SELECT department, AVG(salary) AS avg_salary
FROM Employee
GROUP BY department;

Explanation:

• SELECT department, AVG(salary) AS avg_salary:
  • Retrieves each department along with its average salary.
• FROM Employee:
  • Indicates the source table containing employee data.
• GROUP BY department:
  • Aggregates the data by department to calculate the average salary per department.

Output After Step 1:

+------------+------------+
| department | avg_salary |
+------------+------------+
| A          | 110.00     |
| B          | 117.50     |
| C          | 95.00      |
+------------+------------+

Step 2: Identify High Earners

Find employees whose salaries are higher than their respective department's average salary.

SQL Query:

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);

Explanation:

• SELECT e.id, e.name, e.department, e.salary, AVG(e2.salary) AS avg_salary:
  • Selects employee details along with the average salary of their department.
• FROM Employee e JOIN Employee e2 ON e.department = e2.department:
  • Performs a self-join on the Employee table to associate each employee with others in the same department.
• GROUP BY e.id, e.name, e.department, e.salary:
  • Groups the data by employee to calculate the average salary per department for comparison.
• HAVING e.salary > AVG(e2.salary):
  • Filters the results to include only those employees whose salaries exceed the department average.

Output After Step 2:

+-----+------+------------+--------+------------+
| id  | name | department | salary | avg_salary |
+-----+------+------------+--------+------------+
| 105 | Anne | B          | 130    | 117.50     |
| 107 | Bob  | B          | 125    | 117.50     |
+-----+------+------------+--------+------------+

Step 3: Aggregate High Earners by Department

Count the number of high earners in each department to identify departments meeting the required criteria.

SQL Query:

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 subquery
GROUP BY department;

Explanation:

• Inner Subquery:
  • Identifies high earners as established in Step 2.
• SELECT department, COUNT(*) AS high_earners:
  • Counts the number of high earners in each department.
• FROM ( ... ) AS subquery:
  • Utilizes the results from the high earners identification.
• GROUP BY department:
  • Aggregates the counts by department.

Output After Step 3:

+------------+--------------+
| department | high_earners |
+------------+--------------+
| B          | 2            |
+------------+--------------+

Step 4: Select Eligible Departments

Retrieve departments that have at least two high earners.

SQL Query:

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 subquery
    GROUP BY department
    HAVING COUNT(*) >= 2
) AS result
ORDER BY department;

Explanation:

• Middle Subquery (SELECT department, COUNT(*) AS high_earners ... HAVING COUNT(*) >= 2):
  • Filters departments to include only those with two or more high earners.
• SELECT department FROM ( ... ) AS result:
  • Extracts the department names from the filtered results.
• ORDER BY department:
  • Sorts the final output alphabetically by department name.

Final Output:

+------------+
| department |
+------------+
| B          |
+------------+

I'm working on the problem **Departments with High Earning Employees** (Databases). Give me a HINT LADDER: start with the tiniest nudge, then wait. Only reveal the next, stronger hint when I ask. Do NOT show the full solution unless I type 'show solution'. Keep me doing the thinking. If you're unsure or a claim isn't standard, say so and reason from first principles instead of guessing.

Explain the optimal approach to **Departments with High Earning Employees** with a VISUAL walkthrough: trace it on a small concrete example using ASCII art / a step-by-step diagram, narrate what changes each step, then give time & space complexity with a one-line derivation. If you're unsure or a claim isn't standard, say so and reason from first principles instead of guessing.

I'll paste my solution to **Departments with High Earning Employees**. Review it for correctness, missed edge cases, and time/space complexity, then coach me toward the optimal — don't just rewrite it. Ask me to paste my code now. If you're unsure or a claim isn't standard, say so and reason from first principles instead of guessing.

Give me 2 problems that use the SAME underlying pattern as **Departments with High Earning Employees**. For each, let me attempt first, then review my answer and name the trigger signal that reveals the pattern. If you're unsure or a claim isn't standard, say so and reason from first principles instead of guessing.