CTEs & Window Functions SQL practice problemHardVerified answer

Second-Highest Salary Per Department (NTH_VALUE)

For every employee, show the second-highest salary in their department using NTH_VALUE with a full-partition frame. Departments with only one employee show NULL. Return department_name, first_name, last_name, salary, second_highest_salary — ordered by department_name, salary DESC, employee_id.

  • CTEs
  • Window functions
  • Joins
  • Sorting

Interview brief

Understand the request

For every employee, show the second-highest salary in their department using NTH_VALUE with a full-partition frame. Departments with only one employee show NULL. Return department_name, first_name, last_name, salary, second_highest_salary — ordered by department_name, salary DESC, employee_id.

Data you will use

Review the relevant tables before deciding how to join, filter, or aggregate them.

employees

  • employee_idINTEGER
  • first_nameVARCHAR(50)
  • last_nameVARCHAR(50)
  • salaryINTEGER
  • department_idINTEGER

departments

  • department_idINTEGER
  • department_nameVARCHAR(50)

Hints, when you need them

Open one clue at a time so you still do the reasoning.

Hint 1

NTH_VALUE(salary, 2) with ORDER BY salary DESC returns the 2nd-highest in the partition.

Hint 2

Like LAST_VALUE, it needs the full-partition frame UNBOUNDED PRECEDING … UNBOUNDED FOLLOWING.

Hint 3

Single-employee departments have no 2nd value → NULL.

Verified SQL answer

Attempt the problem first, then compare structure and reasoning—not just syntax.

Reveal solution and explanation
SELECT d.department_name, e.first_name, e.last_name, e.salary, NTH_VALUE(e.salary, 2) OVER (PARTITION BY e.department_id ORDER BY e.salary DESC ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS second_highest_salary FROM employees e INNER JOIN departments d ON e.department_id = d.department_id ORDER BY d.department_name, e.salary DESC, e.employee_id;

Why this works

NTH_VALUE plucks the value at a specific position in the ordered window — handy for 'the runner-up', 'the 3rd-place', etc. It shares LAST_VALUE's frame requirement: without the full-partition frame you'd only see values up to the current row.

Expected result

Use this output to verify values, aliases, ordering, and row count.

department_namefirst_namelast_namesalarysecond_highest_salary
EngineeringMichaelScott180000130000
EngineeringDavidMartinez130000130000
EngineeringEmilyChen125000130000
EngineeringRobertWilliams95000130000
EngineeringLisaAnderson90000130000
EngineeringAmandaWhite75000130000
ExecutiveJenniferKing250000NULL
FinanceJessicaMoore10500070000
FinanceDanielLee7000070000
Human ResourcesPatriciaDavis9500065000

Previewing 10 of 15 expected rows. Run the query in the editor to inspect the full result.

Learn the concepts behind this answer

Strengthen your understanding with these targeted learning topics: