Above Their Station

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

Domain

SQL

Difficulty

medium

Seniority

mid

Asked in

Deloitte

Problem

Find every employee in employees who earns more than their direct manager, showing the employee's name and salary, the manager's name and salary, and the pay gap between the two. The list runs from the widest gap down to the narrowest, with equal gaps in alphabetical order by employee name.

Sometimes the report out-earns the boss. Find where the ladder bends.

Tables

employees

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

Expected output

Worked solution and explanation

Why this problem exists in real interviews

This is a self-join wearing a compensation-audit costume. The whole skill being probed: can you make one table play two roles at once, an employee row and the manager row it points to, in the same query? Everyone knows employees has manager_id. The trick is realizing manager_id is a foreign key back into employees itself, so you join the table to a second alias of itself on e.manager_id = m.employee_id. Miss that and you start reaching for a correlated subquery that recomputes the manager lookup per row, or you join on the wrong key and silently match employees to themselves.

Break down the requirements

Step 1: Alias the table twice

Bring in employees as e (the report) and employees as m (the boss). Two aliases of one physical table is the move people freeze on; once you name them, the rest is ordinary join logic.

Step 2: Join on the hierarchy edge

The link is e.manager_id = m.employee_id. An inner join here is correct on purpose: top-level people whose manager_id is NULL match nobody and drop out, which is what you want since they have no boss to out-earn.

Step 3: Filter and project the gap

Keep rows where e.salary > m.salary, then expose both salaries and compute e.salary - m.salary AS salary_gap so the reviewer can see the magnitude, not just the fact.

Step 4: Sort widest first, names break ties

ORDER BY salary_gap DESC puts the most upside-down reporting lines at the top, which is exactly what an audit wants to act on first. Several pairs share the same gap on this data, so employee_name follows as the second key to settle them alphabetically.

The solution

SELECT e.emp_name AS employee_name,
       e.salary AS employee_salary,
       m.emp_name AS manager_name,
       m.salary AS manager_salary,
       e.salary - m.salary AS salary_gap
FROM employees e
JOIN employees m ON e.manager_id = m.employee_id
WHERE e.salary > m.salary
ORDER BY salary_gap DESC, employee_name

Common follow-up questions

• Now show every employee who earns more than the average salary of their own department. (Pushes from a self-join to a correlated or windowed AVG over a partition; tests grain awareness.)
• What changes if an employee can have more than one manager through a separate bridge table? (Tests whether they can move from a single self-referencing key to a many-to-many junction join.)
• How would you also flag employees who out-earn their manager's manager, two levels up? (Probes recursive CTEs or a second self-join to walk the hierarchy further.)