Scenario SQL Question

Second-Highest Salary Per Department (NTH_VALUE)

Build mastery in Ctes Window with this scenario-based SQL challenge and live execution support.

Problem Statement

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.

Relevant Tables

employeesdepartments

SQL Concepts Used in This Scenario

Strengthen your understanding with these targeted learning topics:

Common Table ExpressionsSubqueries & CTEs
Window Functions FundamentalsWindow Functions
Running Totals & Moving AveragesWindow Functions