Second Highest Salary
A missão
Problema
Uma tabela employee(id, name, salary) guarda uma linha por pessoa, e várias pessoas podem ganhar o mesmo valor. Escreva uma consulta (query) que retorne exatamente uma linha com uma coluna, second_highest: o segundo maior salário distinto. Quando não houver um segundo salário distinto, a linha ainda precisa vir, contendo NULL.
Exemplos
Input: employee = [(1, "Ana", 300), (2, "Ben", 200), (3, "Cy", 100)]
Output: [(200,)]
Por quê: 300 é o maior, e o maior salário abaixo dele é 200
Input: employee = [(1, "Ana", 300), (2, "Ben", 300), (3, "Cy", 200)]
Output: [(200,)]
Por quê: duas pessoas ganham 300, mas os salários são comparados como valores distintos
Input: employee = [(1, "Ana", 300)]
Output: [(None,)]
Por quê: edge case: uma linha ainda volta, e ela contém NULL
Dicas
0 / 3
Reformule a pergunta: o segundo maior salário é o maior salário entre os que ficam abaixo do salário máximo.
Uma subconsulta (subquery) pode calcular o salário máximo uma vez, e a consulta externa pode então olhar só para os salários abaixo dele.
Calcule MAX(salary) sobre as linhas em que salary < (SELECT MAX(salary) FROM employee). Uma agregação sobre zero linhas ainda retorna uma linha contendo NULL, que é exatamente o edge case que o problema pede.
Restrições
- several people may earn the same salary
Solução
A subconsulta escalar (SELECT MAX(salary) FROM employee) produz um único valor, o salário máximo, e o WHERE externo mantém só as linhas abaixo dele, então um salário máximo repetido é removido de uma vez. O MAX externo então escolhe o melhor do que sobrou. O caso vazio não precisa de código especial: uma agregação sem GROUP BY sempre retorna uma linha, e o MAX de nenhuma linha é NULL. A alternativa popular, ORDER BY salary DESC com LIMIT 1 OFFSET 1 sobre os salários distintos, não retorna linha nenhuma quando não existe segundo salário, então precisa ser envolvida em outro SELECT para virar NULL. A consulta lê a tabela duas vezes, O(n), e um índice em salary transforma os dois máximos em buscas O(log n).
import sqlite3
QUERY = """
SELECT MAX(salary) AS second_highest
FROM employee
WHERE salary < (SELECT MAX(salary) FROM employee)
"""
def run(employees):
db = sqlite3.connect(":memory:")
db.execute("CREATE TABLE employee (id INTEGER PRIMARY KEY, name TEXT, salary INTEGER)")
db.executemany("INSERT INTO employee VALUES (?, ?, ?)", employees)
return db.execute(QUERY).fetchall()
print(run([(1, "Ana", 300), (2, "Ben", 200), (3, "Cy", 100)])) # -> [(200,)]
print(run([(1, "Ana", 300), (2, "Ben", 300), (3, "Cy", 200)])) # -> [(200,)]
print(run([(1, "Ana", 300)])) # -> [(None,)]Travou na ideia, e não no código? Subconsultas explica isso.