Employees who earn more than their manager

Step 1 Solve it yourself first

Tables you will use

employees

What it really tests

Whether you can treat one table as two — a self join — and read a self-referencing key the right way round.

The pattern: Self join

"Their manager", "their parent", "the person who referred them" — a column pointing at another row of the same table.

Picture it

flowchart LR
  H["e: Hana · 80000<br/>manager_id 2"] -->|"m.emp_id = e.manager_id"| B["m: Ben · 70000"]
  B --> C{"80000 more than 70000?"}
  C -->|"yes"| K["keep Hana"]

The approach, step by step

1. Notice that manager_id points to another row of employees.
2. Use the table twice: e for the employee, m for the manager.
3. Join with m.emp_id = e.manager_id (m is e's manager).
4. Keep rows where e.salary > m.salary. An inner join is fine here: people with no manager can't earn more than one.

SELECT e.name AS employee, e.salary,
       m.name AS manager, m.salary AS manager_salary
FROM employees e
JOIN employees m ON m.emp_id = e.manager_id
WHERE e.salary > m.salary
ORDER BY e.name;

SELECT e.name AS employee
FROM employees e
WHERE e.salary > (
  SELECT m.salary
  FROM employees m
  WHERE m.emp_id = e.manager_id
)
ORDER BY e.name;

Step 2 Go further (free, optional)

Optional. The notes above are written to be enough on their own. These free resources give a second explanation and more practice.

• Practice

  LeetCode — Top SQL 50 study plan

  free account

• Practice

  PostgreSQL Exercises — Joins and Subqueries

  pgexercises.com, free

Step 3 Practise

Try each one yourself first: out loud, or written and run in the practice box below, which saves every query you run. Then open the answer and compare.

1. ExplainSay what it does and when to use it, in plain words, with no notes.

   Explain what each alias stands for and why the join condition is written that way round.

   Show answer

   The table is used twice. e is the employee: the row we are asking about. m is the manager: a second copy of the same table, used to look up the boss.

   m.emp_id = e.manager_id reads: "find the row in m whose id is the manager_id written on the employee's row". For Hana, manager_id is 2, so m is Ben's row.

   Written the other way round, e.emp_id = m.manager_id, the roles swap: m becomes someone who reports to e. Then e.salary > m.salary would find managers who earn more than their reports, which is nearly everyone.

2. Writefrom a blank page, then run it

   Find employees who were hired before their manager.

   Show answer

   The same self join, comparing hire dates instead of salaries. In this data nobody was hired before their manager: Asha (2019) came before everyone she manages, and so did Dara and Ben (2020). An empty result is the right answer here.

   sqlCopy

   SELECT e.name AS employee, e.hire_date,
          m.name AS manager, m.hire_date AS manager_hired
   FROM employees e
   JOIN employees m ON m.emp_id = e.manager_id
   WHERE e.hire_date < m.hire_date
   ORDER BY e.name;

   Try it

   No rows

   employee	hire_date	manager	manager_hired

3. Predictsay it before you run it

   If Eli's salary rose to 61000, which rows would the solution return?

   Show answer

   Two rows: Eli (61,000) over his manager Dara (60,000), and Hana (80,000) over Ben (70,000), as before. Sorted by name, Eli comes first.

   sqlCopy

   UPDATE employees SET salary = 61000 WHERE name = 'Eli';
   SELECT e.name AS employee, e.salary,
          m.name AS manager, m.salary AS manager_salary
   FROM employees e
   JOIN employees m ON m.emp_id = e.manager_id
   WHERE e.salary > m.salary
   ORDER BY e.name;

   Try it

   2 rows

   employee	salary	manager	manager_salary
   Eli	61000	Dara	60000
   Hana	80000	Ben	70000

4. Edge casesSay what happens with NULLs, ties, duplicates or no rows at all, and fix it.

   Change the solution to also list employees with no manager, showing NULL for the manager columns.

   Show answer

   Use a LEFT JOIN so employees with no manager keep their row (with NULLs), and let them through the WHERE on purpose: e.salary > m.salary is unknown when m is NULL, so add OR m.emp_id IS NULL. Asha now appears with NULL manager columns, next to Hana.

   sqlCopy

   SELECT e.name AS employee, e.salary,
          m.name AS manager, m.salary AS manager_salary
   FROM employees e
   LEFT JOIN employees m ON m.emp_id = e.manager_id
   WHERE e.salary > m.salary OR m.emp_id IS NULL
   ORDER BY e.name;

   Try it

   2 rows

   employee	salary	manager	manager_salary
   Asha	95000	NULL	NULL
   Hana	80000	Ben	70000

Step 4 Interview-ready, then prove it

Your interview answer

About 20 to 40 seconds, said out loud.

manager_id references the same table, so I self-join employees as e and m, matching the manager alias's emp_id to the employee's manager_id, and filter where the employee's salary is greater than the manager's. An inner join is right here because someone without a manager can't out-earn one.

Follow-up questions

Tick each check when you can do it without notes