Back to questions

Well Paid Employees [FAANG SQL Interview Question]

Easy

FAANG

Companies often perform salary analyses to ensure fair compensation practices. One useful analysis is to check if there are any employees earning more than their direct managers.

As a HR Analyst, you're asked to identify all employees who earn more than their direct managers. The result should include the employee's ID and name.

Schema:

column_name	type	description
employee_id	integer	The unique ID of the employee.
name	string	The name of the employee.
salary	integer	The salary of the employee.
department_id	integer	The department ID of the employee.
manager_id	integer	The manager ID of the employee.

Example Input:

employee_id	name	salary	department_id	manager_id
1	Emma Thompson	3800	1	6
2	Daniel Rodriguez	2230	1	7
3	Olivia Smith	7000	1	8
4	Noah Johnson	6800	2	9
5	Sophia Martinez	1750	1	11
6	Liam Brown	13000	3	NULL
7	Ava Garcia	12500	3	NULL
8	William Davis	6800	2	NULL

Example Output:

employee_id	employee_name
3	Olivia Smith

The output shows that Olivia Smith earns $7,000, surpassing her manager, William David who earns $6,800.

The dataset you are querying against may have different input & output - this is just an example!

Solution

Step 1: Compare Employees and Managers' Salaries

First, we perform an where we treat the first table () as the managers' table and the second table () as the employees' table. We join on the manager's matching the employee's . I know this can be a bit confusing at first, so check out the Self-Join tutorial if you need a refresher.

Also, maybe just run the query to see how it works!!? We're including all 6 columns in the query to help explain the logic behind it:

This will show all manager-employee pairs. For example:

manager_id	manager_name	manager_salary	employee_id	employee_name	employee_salary
7	Ava Garcia	12500	15	Amelia Lee	4000
7	Ava Garcia	12500	2	Daniel Rodriguez	2230
8	William Davis	6800	12	Benjamin Hernandez	9500
8	William Davis	6800	3	Olivia Smith	7000

Step 2: Filter Employees Who Earn More Than Their Managers

Next, we add a clause to filter the results, ensuring we only get employees whose salaries are higher than their managers'.

The clause filters the results to include only those rows where an employee's salary is greater than their manager's salary.

This will display employees who earn more than their managers. For example:

manager_id	manager_name	manager_salary	employee_id	employee_name	employee_salary
8	William Davis	6800	3	Olivia Smith	7000
8	William Davis	6800	12	Benjamin Hernandez	9500

The output reveals that both Olivia Smith ($7,000) and Benjamin Hernandez ($9,500) earn more than their manager, William Davis, who earns $6,800. It looks like William Davis may need a significant salary revision.

Step 3: Display Only Relevant Employee Information

Finally, we refine the output to match the required format, which includes only the employee's ID and name.

employee_id	employee_name
3	Olivia Smith
12	Benjamin Hernandez

PostgreSQL 14