Segundo salario más alto
La misión
Problema
Una tabla employee(id, name, salary) guarda una fila por persona, y varias personas pueden ganar lo mismo. Escribe una consulta (query) que devuelva exactamente una fila con una columna, second_highest: el segundo salario distinto más alto. Cuando no existe un segundo salario distinto, la fila igual tiene que aparecer, con NULL como valor.
Ejemplos
Input: employee = [(1, "Ana", 300), (2, "Ben", 200), (3, "Cy", 100)]
Output: [(200,)]
Por qué: 300 es el más alto, y el salario más alto por debajo de él es 200
Input: employee = [(1, "Ana", 300), (2, "Ben", 300), (3, "Cy", 200)]
Output: [(200,)]
Por qué: dos personas comparten 300, pero los salarios se comparan como valores distintos
Input: employee = [(1, "Ana", 300)]
Output: [(None,)]
Por qué: caso límite: igual vuelve una fila, y contiene NULL
Pistas
0 / 3
Reformula la pregunta: el segundo salario más alto es el salario más alto entre los que quedan por debajo del salario máximo.
Una subconsulta puede calcular el salario máximo una sola vez, y después la consulta externa puede mirar solo los salarios que están por debajo.
Toma MAX(salary) sobre las filas donde salary < (SELECT MAX(salary) FROM employee). Una agregación sobre cero filas igual devuelve una fila con NULL, que es justo el caso límite que pide el problema.
Restricciones
- several people may earn the same salary
Solución
La subconsulta escalar (SELECT MAX(salary) FROM employee) produce un solo valor, el salario máximo, y el WHERE externo conserva solo las filas por debajo de él, así que un salario máximo repetido se elimina de una vez. Luego el MAX externo elige el mayor de lo que queda. El caso vacío no necesita código especial: una agregación sin GROUP BY siempre devuelve una fila, y el MAX de cero filas es NULL. La alternativa popular, ORDER BY salary DESC con LIMIT 1 OFFSET 1 sobre los salarios distintos, no devuelve ninguna fila cuando no hay segundo salario, así que hay que envolverla en otro SELECT para convertir eso en NULL. La consulta lee la tabla dos veces, O(n), y un índice sobre salary convierte ambos máximos en búsquedas 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,)]¿Lo que te frena es la idea y no el código? Subconsultas lo explica.