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 Listsubordinates; }; // 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 IListGetEmployeesEarningMoreThanTheirManagers(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; }