logo

Back to questions

Compensation Outliers

Medium

Your team at Accenture is helping a Fortune 500 client revamp their compensation and benefits program. The first step in this analysis is to manually review employees who are potentially overpaid or underpaid.

An employee is considered to be potentially overpaid if they earn more than 2 times the average salary for people with the same title. Similarly, an employee might be underpaid if they earn less than half of the average for their title. We'll refer to employees who are both underpaid and overpaid as compensation outliers for the purposes of this problem.

Write a query that shows the following data for each compensation outlier: employee ID, salary, and whether they are potentially overpaid or potentially underpaid.

Table:

Column NameType
employee_idinteger
salaryinteger
titlevarchar

Example Input:

employee_idsalarytitle
10180000Data Analyst
10290000Data Analyst
103100000Data Analyst
10430000Data Analyst
105120000Data Scientist
106100000Data Scientist
10780000Data Scientist
108310000Data Scientist

Example Output:

employee_idsalarystatus
10430000Underpaid
108310000Overpaid

Explanation

In this example, 2 employees qualify as compensation outliers. Employee 104 is a Data Analyst, and the average salary for this position is $75,000. Meanwhile, the salary of employee 104 is less than $37,500 (half of $75,000); therefore, they are underpaid.

PostgreSQL 14

Refer friends to get bonus content & cool prizes.