Higher Than Supervisor

An easy SQL interview practice problem on DataDriven. Write the query and run it against a real database, with instant feedback.

Domain

SQL

Difficulty

easy

Seniority

L4

Asked in

Chewy

Problem

Every row of employees names its direct manager through manager_id, that manager's own employee_id, and the top-level people point to no one. List the employees paid more than their direct manager, with both names and salaries side by side, from the widest pay gap down and alphabetically by employee name where gaps match.

When the student outscores the teacher.

Tables

employees

Columns: employee_id INTEGER, emp_name TEXT, salary REAL, manager_id INTEGER, department TEXT, hire_date TEXT, user_id INTEGER

Expected output

Worked solution and explanation

What this really is

This is a row-to-row comparison inside one table, dressed up as a pay audit. The manager's salary is not a column on the employee's row. It sits on a different row of employees, and the only way to reach it is to follow manager_id back to an employee_id. A benchmark like AVG(salary) answers a different question. The inner self-join does both jobs at once: it hands every employee their own manager's row, and it drops anyone whose manager_id is NULL. That is what leaves the 174 rows the grader expects.

Step 1: Alias employees twice

Read the table once as the report (e) and once as the boss (m). Join on e.manager_id = m.employee_id, so the employee's pointer lands on the manager's key. If you reverse it, every row pairs a manager with one of their reports instead.

Step 2: Let the inner join do the exclusion

A NULL manager_id equals nothing, so the top-level people fall out without an extra manager_id IS NOT NULL filter. Nobody has to remember the rule: the join enforces it.

Step 3: Compare across the two aliases

WHERE e.salary > m.salary keeps only the inversions. Use a strict greater-than, because someone paid exactly their manager's salary is not out-earning them.

Step 4: Sort by the gap, then the name

The gap is e.salary - m.salary, largest first. On this table the top of the list has three different pairs tied at a gap of 90,059 (Nia Alexander, Uri Ford, Via Hamilton), so e.emp_name decides who comes first.

The solution

SELECT e.emp_name AS employee, e.salary AS emp_salary, m.emp_name AS manager, m.salary AS mgr_salary FROM employees e JOIN employees m ON e.manager_id = m.employee_id WHERE e.salary > m.salary ORDER BY e.salary - m.salary DESC, e.emp_name

Benchmark against the average

WHERE salary > (SELECT AVG(salary) FROM employees) finds the well paid, not the people out-earning their own boss. It also has no manager name or salary to show.

Benchmark against the manager row

The self-join puts the manager's row beside the employee's, so the comparison and both name and salary pairs come from one matched row.

Common follow-up questions

• How would you show the gap as a percentage of `mgr_salary`? (Division on the matched row, with `NULLIF` guarding a zero manager salary.)
• Who out-earns their skip-level manager? (A second self-join on `m.manager_id = s.employee_id`.)
• What if the copies were genuine data-entry duplicates you had to collapse? (Deduplicate people on a business key before joining, not output rows after.)