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          |
+------------+