Employees Earning More Than Their Managers

Employees Earning More Than Their Managers

What is this problem about?

The Employees Earning More Than Their Managers interview question is a classic SQL problem. You are given an Employee table that contains employee names, their salaries, and the ID of their manager (who is also an employee in the same table). Your task is to write a query that identifies all employees who have a higher salary than their direct manager.

Why is this asked in interviews?

This is a staple database interview pattern asked by companies like Microsoft, Amazon, and Google to test a candidate's understanding of Self-Joins. It evaluates whether you can compare rows within the same table by joining the table to itself. It also tests basic filtering skills using the WHERE clause and the ability to distinguish between the two instances of the table being joined.

Algorithmic pattern used

The problem is solved using a Self-Join.

1. Alias the Employee table twice (e.g., e for employee and m for manager).
2. Join the two aliases on the condition that the employee's manager ID matches the manager's employee ID: e.managerId = m.id.
3. Apply a filter to compare their salaries: e.salary > m.salary.
4. Select the employee's name.

Example explanation

Suppose the Employee table has:

• Joe: salary 70,000, managerId 3
• Sam: salary 60,000, managerId 3
• Max: salary 90,000, managerId NULL (Max is the manager of Joe and Sam)

1. Self-join connects Joe (e) to Max (m) because Joe's managerId is Max's id.
2. Self-join connects Sam (e) to Max (m).
3. Compare: Joe (70k) vs Max (90k). Joe does not earn more.
4. Compare: Sam (60k) vs Max (90k). Sam does not earn more. If Joe's salary was 100,000, he would be returned in the result.

Common mistakes candidates make

• Forgetting the Join: Trying to use a complex subquery which is often less efficient than a join.
• Incorrect Join Condition: Joining on e.id = m.managerId, which would find managers earning more than their employees.
• Null Handling: Not realizing that employees without managers (where managerId is NULL) will automatically be excluded by an INNER JOIN.

Interview preparation tip

Whenever you need to compare records within the same table based on a hierarchical or parent-child relationship, a Self-Join is your go-to tool. Practice assigning clear aliases to the table instances to keep your logic organized.