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.

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 hierarchy lookup wearing a compensation-audit costume. The pay you compare against is not on the employee's row at all: it sits on the manager's row, and manager_id is the only road there. Anyone can write salary > something. What separates candidates is seeing that the something is another row of the same table, reached by report.manager_id = manager.employee_id. If you compare against a team average instead, you flag roughly half of each team. If you walk the key backwards, you get the 6 pairs where the boss earns more, not the 174 where the report does.

Break down the requirements

Step 1: Give the table two roles

Read employees once as report and again as manager. It is one physical table with two aliases. That naming step is where people freeze, and once it is done the rest is an ordinary join.

Step 2: Follow the pointer upward

report.manager_id = manager.employee_id goes from the person to their boss. The 20 rows with a NULL manager_id match nobody and drop out. That is right, because they have no boss to out-earn.

Step 3: Filter strictly, then show the gap

Keep report.salary > manager.salary, which removes 6 of the 180 managed rows on this data. Expose both salaries and report.salary - manager.salary AS salary_gap so the reviewer sees the size of the gap, not only the fact of it.

Step 4: Widest gap first

ORDER BY salary_gap DESC puts the most upside-down reporting lines on top. Several pairs share a gap, and their order relative to each other is not part of the answer, so no second key is needed.

The solution

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

Inverted key

report.employee_id = manager.manager_id pairs each person with their reports. The filter then asks whether a boss out-earns a subordinate, which returns just 6 rows with the roles swapped.

Correct key

report.manager_id = manager.employee_id follows the pointer from the report up to the boss, so report is always the employee and manager is always the manager. It returns all 174 rows.

Common follow-up questions

• Now show every employee who earns more than the average salary of their own department. (Moves from a row-to-row lookup to a comparison against a group statistic; tests grain awareness.)
• What changes if an employee can have more than one manager through a separate bridge table? (Tests moving 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 a second hop up the hierarchy: another alias, or a recursive walk.)