Second Highest Salary

Problem

A table employee(id, name, salary) holds one row per person, and several people may earn the same amount. Write a query that returns exactly one row with one column, second_highest: the second-highest distinct salary. When there is no second distinct salary, the row must still come back, holding NULL.

Examples

Input:  employee = [(1, "Ana", 300), (2, "Ben", 200), (3, "Cy", 100)]
Output: [(200,)]
Why:    300 is the highest, and the highest salary below it is 200

Input:  employee = [(1, "Ana", 300), (2, "Ben", 300), (3, "Cy", 200)]
Output: [(200,)]
Why:    two people share 300, but salaries are compared as distinct values

Input:  employee = [(1, "Ana", 300)]
Output: [(None,)]
Why:    edge case, one row still comes back and it holds NULL

Hints

0 / 3

1. Hint 1reveal
2. Hint 2locked
3. Hint 3locked

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,)]