Solving SQL LeetCode 181: Employees Earning More Than Their Managers

In this article, we’ll tackle LeetCode problem 181, “Employees Earning More Than Their Managers.” We’ll start by understanding the problem, explore a simple solution, and then optimize it for efficiency.

Problem Description

You can find the problem on LeetCode’s website at this link. The problem statement is as follows:

Problem Statement: Given a table Employee, write an SQL query to find employees who earn more than their managers. The Employee table consists of the following columns:

• id: Employee's unique ID
• name: Employee's name
• salary: Employee's salary
• manager_id: ID of the employee's manager

Employee Table

Now, let’s dive into the problem and formulate a solution.

Understanding the Problem

We need to identify employees who earn more than their managers. To do this, we’ll compare the salary of each employee (a) with their manager's (b) salary. This is typically a straightforward task in SQL.

Initial Solution: Cartesian Product

One way to approach this problem is by selecting data from the Employee table twice and then applying a WHERE clause to filter the results based on the condition. To start, we can use a Cartesian product of the Employee table with itself. A Cartesian product is a cross-join that combines every row with every other row.

SELECT * FROM Employee AS a, Employee AS b;

SELECT * FROM Employee AS a, Employee AS b;

The result of this query will be a massive table with all possible combinations of employees and their managers. It’s essential to note that this result is not our final answer; it’s an intermediate step to work with. Here’s a snippet of what the output might look like:

Output Snippet

Filtering with a WHERE Clause

Now that we have all the combinations, we can use a WHERE clause to filter the results based on the condition that an employee's salary should be greater than their manager's salary.

SELECT *
FROM Employee AS a, Employee AS b
WHERE a.manager_id = b.id AND a.salary > b.salary;

This query efficiently identifies employees who earn more than their managers. The WHERE clause ensures that only rows satisfying the condition are selected, and we obtain the desired result.

Output Snippet

We can further modify the above query to get only the name of the employee.

SELECT a.name AS Employee
FROM Employee AS a, Employee AS b
WHERE a.manager_id = b.id AND a.salary > b.salary;

Output Snippet

A More Elegant Solution: JOIN and ON

While the Cartesian product approach works, it’s not the most efficient or elegant solution. We can achieve the same result by joining the Employee table with itself and specifying the condition using the ON keyword. This approach is both readable and efficient.

SELECT a.name AS Employee
FROM Employee AS a
JOIN Employee AS b
ON a.manager_id = b.id AND a.salary > b.salary;

The JOIN clause connects the Employee table to itself, and the ON condition specifies the requirement: the employee's manager_id should match the manager's id, and the employee's salary must be greater than the manager's salary.

Conclusion

Solving LeetCode problem 181, “Employees Earning More Than Their Managers,” involves identifying employees who earn more than their managers. We explored two solutions: the initial Cartesian product approach and the more elegant JOIN and ON approach. The latter is preferred for its clarity and efficiency.