Solution For Employees Earning More Than Their Managers

Problem Statement:

The Employee table holds all employees including their managers. Each employee has an Id, and there is also a column for the manager Id.

+—-+——-+——–+———–+
| Id | Name | Salary | ManagerId |
+—-+——-+——–+———–+
| 1 | Joe | 70000 | 3 |
| 2 | Henry | 80000 | 4 |
| 3 | Sam | 60000 | NULL |
| 4 | Max | 90000 | NULL |
+—-+——-+——–+———–+

Given the Employee table, write a SQL query that finds out employees who earn more than their managers. For the above table, Joe is the only employee who earns more than his manager.
+———-+
| Employee |
+———-+
| Joe |
+———-+

Solution:

To solve this problem, we need to perform a self-join on the Employee table. We will join the table with itself using the ManagerId column to match employees with their managers.

The join condition will be that the employee’s ManagerId column matches with the manager’s Id column. Also, the employee’s Salary must be greater than their manager’s Salary.

SELECT employee.Name as Employee
FROM Employee employee
JOIN Employee manager
ON employee.ManagerId = manager.Id
WHERE employee.Salary > manager.Salary;

Explanation:

We first specify the columns we want to select, which in this case is the employee’s Name.

Next, we perform a join on the Employee table, joining the table with itself using the ManagerId column to match employees with their managers.

We then specify the join condition, which is that the employee’s ManagerId column matches with the manager’s Id column. Also, the employee’s Salary must be greater than their manager’s Salary.

Finally, we use the WHERE clause to filter the result set based on the condition that the employee’s salary is greater than their manager’s salary.

Step by Step Implementation For Employees Earning More Than Their Managers

// This is the Employee class given by LeetCode
class Employee {
    // It's the unique id of each node;
    // unique id of this employee
    public int id;
    // the importance value of this employee
    public int importance;
    // the id of direct subordinates
    public List subordinates;
};

// This is my solution to the problem
public int getImportance(List employees, int id) {
    // This HashMap will store each employee's id as the key and the employee object as the value
    HashMap map = new HashMap<>();
    
    // We will loop through the employees list and add each employee to the HashMap
    for (Employee employee : employees) {
        map.put(employee.id, employee);
    }
    
    // This is our queue that we will use for our breadth-first search
    Queue queue = new LinkedList<>();
    
    // We will start by adding the employee with the given id to our queue
    queue.add(map.get(id));
    
    // This variable will keep track of the total importance value
    int totalImportance = 0;
    
    // We will keep looping as long as our queue is not empty
    while (!queue.isEmpty()) {
        // We will remove the first employee in the queue
        Employee currentEmployee = queue.remove();
        
        // We will add the importance value of the current employee to our total
        totalImportance += currentEmployee.importance;
        
        // We will loop through the current employee's subordinates
        for (int subordinateId : currentEmployee.subordinates) {
            // For each subordinate, we will add them to our queue
            queue.add(map.get(subordinateId));
        }
    }
    
    // We will return the total importance value
    return totalImportance;
}

# Python Solution for Leetcode Problem: Employees Earning More Than Their Managers 

# Input: A list of employee records, each record consisting of a manager's ID, 
# an employee's ID, and the employee's salary 

# Output: A list of employee IDs who earn more than their managers 

# Time Complexity: O(n), where n is the number of employee records 
# Space Complexity: O(n), where n is the number of employee records 

def find_employees_earning_more_than_their_managers(employee_records): 
    
    # key: manager's ID, value: employee's ID and salary 
    manager_to_employees = {} 
    
    # key: employee's ID, value: employee's salary 
    employee_to_salary = {} 
    
    # initialize data structures 
    for record in employee_records: 
        manager_id = record[0] 
        employee_id = record[1] 
        salary = record[2] 
        
        # add employee to manager's list of employees 
        if manager_id not in manager_to_employees: 
            manager_to_employees[manager_id] = [] 
        manager_to_employees[manager_id].append((employee_id, salary)) 
        
        # store employee's salary 
        employee_to_salary[employee_id] = salary 
        
    # initialize list of employees earning more than their managers 
    employees_earning_more_than_their_managers = [] 
    
    # iterate through all managers 
    for manager_id in manager_to_employees: 
        
        # iterate through all employees managed by this manager 
        for employee_id, salary in manager_to_employees[manager_id]: 
            
            # compare employee's salary to manager's salary 
            if salary > employee_to_salary[manager_id]: 
                employees_earning_more_than_their_managers.append(employee_id) 
                
    return employees_earning_more_than_their_managers

// This is the solution to the leetcode problem "employees-earning-more-than-their-managers".

// The problem is as follows:

// You are given the table employee with the following structure:

// employee

// | id | name  | salary | manager_id |

// |----|-------|--------|------------|

// | 1  | Joe   | 70000  | 3          |

// | 2  | Henry | 80000  | 4          |

// | 3  | Sam   | 60000  | NULL       |

// | 4  | Max   | 90000  | NULL       |

// Write a SQL query that finds all employees who earn more than their managers. For the above table, Joe earns more than Sam, and Henry earns more than Max.

SELECT 
    e1.name AS 'Employee',
    e2.name AS 'Manager'
FROM 
    employee AS e1
JOIN 
    employee AS e2 ON e1.manager_id = e2.id
WHERE 
    e1.salary > e2.salary;

#include  
#include  
#include  

using namespace std; 

class Solution { 
public: 
    vector findEmployeesEarningMoreThanTheirManagers(vector> data) { 
        // sort the data according to employee id 
        sort(data.begin(), data.end()); 
        
        // create a vector to store the id's of employees 
        // earning more than their managers 
        vector result; 
        
        // for each employee 
        for (int i = 0; i < data.size(); i++) { 
            // compare the salary of current employee with 
            // the salary of next employee 
            if (data[i][1] < data[i + 1][1]) { 
                // if salary of current employee is less than 
                // the salary of next employee, then push the 
                // id of current employee in the result vector 
                result.push_back(data[i][0]); 
            } 
        } 
        
        // return the vector containing id's of employees 
        // earning more than their managers 
        return result; 
    } 
};

public IList GetEmployeesEarningMoreThanTheirManagers(int[][] input)
        {
            //List to store the output
            List output = new List();

            //Loop through each row in the input array
            for(int i = 0; i < input.Length; i++)
            {
                //Check if the employee's salary is greater than their manager's salary
                if(input[i][2] > input[i][3])
                {
                    //If so, add the employee's id to the output list
                    output.Add(input[i][0]);
                }
            }

            //Return the output list
            return output;
        }