Ganha mais que o próprio gerente
A missão
Problema
Uma tabela employee(id, name, salary, manager_id) guarda todo mundo de uma empresa, e manager_id é o id da pessoa a quem cada um se reporta, ou NULL no topo. Escreva uma consulta (query) que retorne todo funcionário que ganha estritamente mais que o gerente direto, nas colunas name, salary e manager_salary, ordenado por nome.
Exemplos
Input: employee = [(1, "Ana", 120, None), (2, "Ben", 90, 1), (3, "Cleo", 130, 1), (4, "Dev", 95, 3), (5, "Finn", 100, 4)]
Output: [("Cleo", 130, 120), ("Finn", 100, 95)]
Por quê: Cleo ganha mais que Ana, e Finn ganha mais que Dev, que se reporta a Cleo
Input: employee = [(1, "Ana", 100, None), (2, "Ben", 100, 1)]
Output: []
Por quê: edge case: um salário igual não é maior
Dicas
0 / 3
Cada linha precisa de dois salários lado a lado: o do funcionário e o do gerente. Os dois ficam na mesma tabela.
Uma tabela pode fazer join com ela mesma usando dois aliases, um no papel do funcionário e outro no papel do gerente.
Faça JOIN employee m ON m.id = e.manager_id e mantenha WHERE e.salary > m.salary. O inner join descarta a pessoa do topo, que não tem gerente com quem comparar.
Restrições
- manager_id is NULL at the top
Solução
O salário do gerente está em outra linha da mesma tabela, então a tabela faz join com ela mesma: o alias e é o funcionário, o alias m é o gerente, e m.id = e.manager_id coloca cada pessoa ao lado do chefe dela. Depois disso, a comparação é um WHERE comum. O inner join é proposital: a pessoa do topo tem manager_id NULL, não corresponde a nada e desaparece, o que está certo, porque ninguém pode ganhar mais que um gerente que não tem. Uma subconsulta correlacionada (correlated subquery), e.salary > (SELECT salary FROM employee WHERE id = e.manager_id), dá a mesma resposta. Com id como chave primária, cada busca de gerente é O(log n), então a consulta inteira é O(n log n).
import sqlite3
QUERY = """
SELECT e.name, e.salary, m.salary AS manager_salary
FROM employee e
JOIN employee m ON m.id = e.manager_id
WHERE e.salary > m.salary
ORDER BY e.name
"""
def run(employees):
db = sqlite3.connect(":memory:")
db.execute("CREATE TABLE employee (id INTEGER PRIMARY KEY, name TEXT, salary INTEGER, manager_id INTEGER)")
db.executemany("INSERT INTO employee VALUES (?, ?, ?, ?)", employees)
return db.execute(QUERY).fetchall()
print(run([(1, "Ana", 120, None), (2, "Ben", 90, 1), (3, "Cleo", 130, 1), (4, "Dev", 95, 3), (5, "Finn", 100, 4)])) # -> [('Cleo', 130, 120), ('Finn', 100, 95)]
print(run([(1, "Ana", 100, None), (2, "Ben", 100, 1)])) # -> []Travou na ideia, e não no código? JOINs INNER e OUTER explica isso.