Grokking SQL for Tech Interviews
Ask Author
Back to course home

0% completed

Vote For New Content
10. Departments with High Earning Employees (Medium)
Table of Contents

Contents are not accessible

Contents are not accessible

Contents are not accessible

Contents are not accessible

Contents are not accessible

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

MYSQL
MYSQL

. . . .

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

.....

.....

.....

Like the course? Get enrolled and start learning!

Table of Contents

Contents are not accessible

Contents are not accessible

Contents are not accessible

Contents are not accessible

Contents are not accessible