Second-Highest Salary per Department
For each department, find the second-highest distinct salary. If several people share the top salary, the second-highest is the next lower value. Departments with fewer than two distinct salaries are left out. NULL salaries are ignored.
Return columns dept, second_salary. Row order does not matter.
Schema
CREATE TABLE employees ( emp_id INTEGER PRIMARY KEY, name TEXT NOT NULL, dept TEXT NOT NULL, salary INTEGER );
Sample data
employees
| emp_id | name | dept | salary |
|---|---|---|---|
| 1 | Adel | Engineering | 14000 |
| 2 | Bea | Engineering | 17500 |
| 3 | Cem | Engineering | 12000 |
| 4 | Dina | Finance | 11000 |
| 5 | Emre | Finance | 9500 |
| 6 | Fay | Sales | 8000 |
| 7 | Gus | Engineering | 15200 |
Hint 1
DENSE_RANK gives tied salaries the same rank and does not skip the next number.
Hint 2
Several people can share the second-highest salary, so remove duplicate rows.
Good practice for: Data Analyst, Backend Developer, Database Administrator, Data Engineer
SQL (SQLite)
Run checks your query on the sample data (Ctrl + '). Submit also checks hidden databases.