The Runner-Up Salary
The compensation team at Kestrel Labs is drawing the top of the pay structure and already knows the ceiling. What they need next is the rung below it: the largest salary figure on file that is not the largest one overall. That is the number the next pay band is anchored to.
They asked for the figure, not the person. If several people happened to be paid the top amount, they are all still on the same rung, and the answer is the next amount down — so the question is about amounts, not about sorting people and taking the one in second place.
employees — one row per person. department_id is the team they sit on, given as the matching id in departments; salary is annual pay in dollars; hire_date is the day they started, written year-month-day; manager_id is the id of the person they report to, and is NULL for anyone who reports to nobody.
| id | name | department_id | salary | hire_date | manager_id |
|---|---|---|---|---|---|
| 1 | Ava Chen | 1 | 145000 | 2021-03-14 | NULL |
| 2 | Ben Ortiz | 1 | 118000 | 2022-06-01 | 1 |
| 3 | Cara Novak | 1 | 121000 | 2023-01-10 | 1 |
| 4 | Deshawn Lee | 2 | 95000 | 2020-09-23 | NULL |
| 5 | Elin Kask | 2 | 88000 | 2022-11-05 | 4 |
| 6 | Farid Amiri | 3 | 76000 | 2023-04-18 | NULL |
| 7 | Grace Kim | 4 | 132000 | 2021-07-30 | NULL |
| 8 | Hugo Silva | 4 | 110000 | 2023-02-14 | 7 |
| 9 | Ines Duarte | 4 | 104000 | 2023-08-01 | 7 |
| 10 | Jonas Weber | 2 | 91000 | 2021-12-19 | 4 |
The table already exists in the database — there is nothing to create or load.
Task: Write a query returning one row with a single column, second_salary, holding the largest salary value that is strictly smaller than the largest salary value in the table. If several employees were paid the top amount they would all be skipped together, since the answer is the next amount down rather than the second person in a list.
Example output — the shape, on an invented figure. One row, one column, a bare salary value:
| second_salary |
|---|
| 96500 |
Sign in to solve this problem
Reading problems is free for everyone — solving them (Run, Submit, and tracking what you've solved) needs an account.
Sign in