Near-salary pairs in a department

SQL data engineering interview problem. Difficulty: advanced. Pattern: Self Joins. About 18 minutes. Part of the Pro drill bank.

HR wants to find employees in the same department whose pay is almost the same, which is a first step when checking if pay is fair. Find pairs of employees who are in the same department (not NULL) and whose salaries differ by less than 5000. Return employee_1 and employee_2 (the two names), department and salary_diff (the absolute difference of the two salaries). List each pair only once, with employee_1 coming before employee_2 alphabetically. Ignore NULL salaries. Sort by department, employee_1, employee_2.

Requirements

• Compare employees with each other inside the same department.
• Keep only pairs where the absolute salary difference is less than 5000.
• List each pair once, using a.name < b.name so the same pair is not shown twice.
• Ignore NULL salaries and NULL departments.
• Sort by department, employee_1, employee_2.

Constraints

• The table is employees, joined to itself with two aliases.
• salary_diff is ABS(a.salary - b.salary).
• Return only these 4 columns, in this order: employee_1, employee_2, department, salary_diff.

Examples

Input: employees (Engineering) name | department | salary Bob Kumar | Engineering | 95000 Eve Ng | Engineering | 95000 Dan Park | Engineering | 110000 Output: employee_1 | employee_2 | department | salary_diff Bob Kumar | Eve Ng | Engineering | 0 Why this passes: Dan differs by 15000 from Bob, so that pair is excluded by the < 5000 rule.

Topics: lakebench, sql, self-join, pairs.

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