0% completed
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.
- Performs a self-join on the
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 | +------------+
.....
.....
.....
Table of Contents
Contents are not accessible
Contents are not accessible
Contents are not accessible
Contents are not accessible
Contents are not accessible