Second highest salary

Write a SQL query to find the second highest salary from an employees table.

sql sqlite query subquery aggregation

You are given a SQLite table named `employees` with the following schema:

```sql
CREATE TABLE employees (
  id INTEGER PRIMARY KEY,
  salary INTEGER
);
```

Write a function `second_highest_salary()` that connects to an in-memory SQLite database, creates this table, inserts the given sample data, and returns the second highest distinct salary. If there is no second highest distinct salary (i.e., fewer than 2 distinct salaries), return `None`.

Your function should:
- Use the `sqlite3` module (already imported).
- Create an in-memory database.
- Execute the `CREATE TABLE` statement above.
- Insert all rows from the `data` parameter.
- Run a single SQL query to find the second highest distinct salary.
- Close the connection and return the result as an integer or `None`.

The function signature is:
```python
def second_highest_salary(data: list[tuple[int, int]]) -> int | None:
```

`data` is a list of `(id, salary)` tuples. The `id` values are unique integers; the `salary` values may have duplicates. The order of rows is not guaranteed. Return the second highest distinct salary, or `None` if there is no such value.

Constraints

- The number of rows in `data` is between 0 and 1000.
- Each `id` is unique.
- `salary` values are integers (can be negative).
- The result should be an integer or `None` (Python `None`, not SQL NULL).

Example

>>> second_highest_salary([(1, 100), (2, 200), (3, 300)])
200
>>> second_highest_salary([(1, 100), (2, 100)])
None
>>> second_highest_salary([])
None

8 points ~10 min

Recent Submissions

No submissions yet — hit Run Tests to try!

Hints

Use a subquery with DISTINCT to get unique salaries sorted in descending order.

Use LIMIT 1 OFFSET 1 inside the subquery.

Remember that if the subquery returns no rows, the outer query should return NULL.

Fetch the scalar result and convert it to Python int or None.

Test Results

Press Ctrl+Enter or click Run Tests to execute your code.