# Above Their Station > Sometimes the report out-earns the boss. Find where the ladder bends. Canonical URL: Domain: SQL · Difficulty: medium · Seniority: mid ## Problem A workforce analytics team is auditing pay structure and wants to spot people who out-earn the person they report to. List each such employee next to their manager with both salaries, widest pay gap at the top. ## 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 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. --- ### The solution **Reports who out-earn their manager** ```sql 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 ``` > **Trick to solving** > > The instant you see a column that points back to the same table's primary key (manager_id to employee_id), the answer is a self-join, not a subquery. Two aliases, join on the edge, and the hierarchy unfolds in one pass. > **Common Pitfall** > > Using a LEFT JOIN here. It feels safer, but it drags in every employee whose manager row is NULL, and those rows then either fail the salary comparison silently or, worse, get kept if you forget the comparison handles NULL. The inner join is the correct, intentional choice: no manager, no comparison. > **Cost Analysis** > > On a 2M-row HR warehouse table (roughly 400 MB), this self-join is a hash join keyed on employee_id, which is the primary key, so the build side fits cheaply and the probe is one scan. An index on manager_id lets the planner avoid scanning twice. The salary comparison and the gap arithmetic are evaluated row-local, so they add nothing to the plan's cost. > **Interviewers Watch For** > > Whether you say out loud that manager_id references employees, and whether you reason about NULL managers before being asked. A candidate who states 'inner join drops the top of the org, which is fine here' has shown they understand the data, not just the syntax. --- ## 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.)_ ## Related - [All practice problems](https://datadriven.io/problems) - [Mock interview mode](https://datadriven.io/interview/above-their-station) - [SQL Interview Questions](https://datadriven.io/sql-interview-questions) - [Data Engineering Interview Prep Guide](https://datadriven.io/data-engineer-interview-prep) - [Daily Challenge](https://datadriven.io/daily) --- Source: DataDriven (https://datadriven.io). 100% free data engineering interview prep. Live code execution against Postgres 16, Python 3.11, and Spark sandboxes. No paywall, no premium tier, no signup gate.