Employees earning more than their manager

SQL data engineering interview problem. Difficulty: beginner. Pattern: Self Joins. About 8 minutes. Free to practice.

In org_staff each person has a manager_id pointing to another row of the same table. You want to find the people who are paid more than their own manager. Return every employee whose salary is strictly greater than their manager's salary. Return employee_name, employee_salary, manager_name and manager_salary, sorted by employee_id.

Requirements

• Join org_staff to itself, matching manager_id to the manager's employee_id.
• Keep only rows where the employee's salary is greater than the manager's.
• Equal salaries do not count.
• Return the 4 columns, sorted by employee_id.

Constraints

• The table is org_staff with the columns employee_id, name, salary and manager_id.
• Use two aliases, e for the employee and m for the manager.
• Return only these 4 columns, in this order: employee_name, employee_salary, manager_name, manager_salary.

Examples

Input: org_staff employee_id | name | salary | manager_id 1 | Priya | 150000 | NULL 2 | Quinn | 160000 | 1 3 | Ravi | 90000 | 1 4 | Sia | 95000 | 3 5 | Tom | NULL | 1 6 | Uma | 90000 | 3 7 | Vik | 70000 | 9 Output: employee_name | employee_salary | manager_name | manager_salary Quinn | 160000 | Priya | 150000 Sia | 95000 | Ravi | 90000 Why this passes: Quinn earns more than Priya and Sia earns more than Ravi. Uma ties with Ravi, Tom has no salary, and Vik's manager row does not exist.

Input: org_staff employee_id | name | salary | manager_id 1 | Boss | 100 | NULL 2 | Ann | 120 | 1 3 | Ben | 80 | 1 Output: employee_name | employee_salary | manager_name | manager_salary Ann | 120 | Boss | 100 Why this passes: One employee out-earns the manager and one does not.

Topics: lakebench, sql, self join, manager.

More SQL interview questions · All interview problems · Learn data engineering