Employees Who Earn More Than Their Manager in Python

An interviewer asks: which employees earn more than their manager? The check adds an employees table to shop.db. Its manager_id is the id of each person's manager, which is a row of the same table. Rosa Diaz runs the shop, so her manager_id is NULL.

id | name | salary | manager_id
1 | Rosa Diaz | 6000 | NULL
2 | Tom Clark | 4200 | 1
3 | Ivy Chen | 4500 | 1
4 | Sam Patel | 4400 | 2
5 | Lena Fischer | 4200 | 2
6 | Omar Haddad | 3600 | 4
7 | Nina Kowalski | 3900 | 4
8 | Paul Martin | 4700 | 3
9 | Zoe Becker | 3700 | 6

The idea in one sentence: join the table to itself, so every row holds an employee and their manager side by side, then compare the two salaries with a plain WHERE. To read one table twice, give it two aliases. Here is the same trick on a table of categories, where each category may sit inside a parent category:

SELECT c.name AS category, p.name AS parent
FROM categories c
JOIN categories p ON p.id = c.parent_id

A plain JOIN leaves out the rows that find no match, like a category with no parent.

The check prints your query's rows:

employee | salary | manager | manager_salary
Sam Patel | 4400 | Tom Clark | 4200
Paul Martin | 4700 | Ivy Chen | 4500
Zoe Becker | 3700 | Omar Haddad | 3600
(3 rows)

Your task: in query, select the employee's name as employee, their salary, the manager's name as manager and the manager's salary as manager_salary, from employees joined to itself, keeping only the employees who earn more than their manager.

Assignment
Follow the Coding Tutorial to build the self-join, one clause per line.

Hint
Name the table e for the employee and m for the manager, and join on m.id = e.manager_id. Lena Fischer earns exactly what her manager earns, so a strict > leaves her out.