10. Departments With High Earning Employees (medium)

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

Database Exercise

Database Schema:


-- Database schema would be rendered here

Exercise Script:


-- Exercise script would be rendered here

Available actions: Execute

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