Find employees who earn less than the average department salary

TechieBytes Solutions, a prominent technology firm specializing in software development and IT services, emphasizes innovation and fair employee compensation. As a data analyst at TechieBytes Solutions, you're tasked with identifying disparities in salary distributions across departments to ensure equitable compensation strategies. This analysis supports the company's commitment to maintaining a fair and competitive work environment.

Problem Statement:

Find the details of the employees who earn less than the average salary in their respective departments.

Note:

• Return the columns 'employee_id', 'first_name', 'last_name', 'department_id', and 'salary'.

• Return the output ordered by employee_id in ascending order.

Dataset Description:

Sample Input:

Table: Employees

Sample Output:

Approach 1: Using Subquery

select
    e.employee_id,
    e.first_name,
    e.last_name,
    e.department_id,
    e.salary
from
    employees e,
    (
        select
            department_id,
            avg(salary) as average_salary
        from
            employees
        group by
            department_id
    ) as t
where
    e.department_id = t.department_id
    and e.salary < t.average_salary
order by
    employee_id

Approach 2: Using a Common Table Expression (CTE)

WITH
    AvgSalaries AS (
        SELECT
            department_id,
            AVG(salary) AS average_salary
        FROM
            employees
        GROUP BY
            department_id
    )
SELECT
    e.employee_id,
    e.first_name,
    e.last_name,
    e.department_id,
    e.salary
FROM
    employees e
    JOIN AvgSalaries a ON e.department_id = a.department_id
WHERE
    e.salary < a.average_salary
ORDER BY
    e.employee_id;

Explanation:

• CTE (ManagersWithSubordinates): Identifies managers who have more than 3 subordinates.

• Main Query: Retrieves full names of employees who are managers identified in the CTE.

• ORDER BY: Sorts the results by full_name.

Approach 3: Using Window Functions

SELECT
    employee_id,
    first_name,
    last_name,
    department_id,
    salary
FROM
    (
        SELECT
            e.*,
            AVG(salary) OVER (
                PARTITION BY
                    department_id
            ) AS average_salary
        FROM
            employees e
    ) sub
WHERE
    salary < average_salary
ORDER BY
    employee_id;

Explanation:

• Window Function (AVG): Calculates the average salary for each department without grouping.

• Main Query: Filters out employees whose salary is less than the calculated average salary.

Approach 4: Correlated Subquery

SELECT
    e.employee_id,
    e.first_name,
    e.last_name,
    e.department_id,
    e.salary
FROM
    employees e
WHERE
    e.salary < (
        SELECT
            AVG(salary)
        FROM
            employees
        WHERE
            department_id = e.department_id
    )
ORDER BY
    e.employee_id;

Explanation:

Correlated Subquery: For each employee, the subquery calculates the average salary of the employee's department and filters based on the condition.

Approach 5: Using Derived Table in JOIN

SELECT
    e.employee_id,
    e.first_name,
    e.last_name,
    e.department_id,
    e.salary
FROM
    employees e
    JOIN (
        SELECT
            department_id,
            AVG(salary) AS average_salary
        FROM
            employees
        GROUP BY
            department_id
    ) a ON e.department_id = a.department_id
WHERE
    e.salary < a.average_salary
ORDER BY
    e.employee_id;

Explanation:

• Derived Table: The subquery in the JOIN clause calculates the average salary for each department.

• Main Query: Joins the derived table result with the employees table to filter out employees whose salary is less than the department's average salary.

This article discusses various SQL approaches to identify employees at TechieBytes Solutions who earn less than the average salary in their respective departments. It outlines five different methods: simple join, common table expressions (CTEs), window functions, correlated subqueries, and derived tables in JOINs, each with explanations to ensure fair and competitive compensation strategies.