Given the table Employees with columns id, department, and salary, what will the query below return?

medium📝 query result Q4 of 15

SQL - Advanced Window Functions

Given the table Employees with columns id, department, and salary, what will the query below return?

SELECT id, salary, NTH_VALUE(salary, 2) OVER (PARTITION BY department ORDER BY salary DESC) AS second_highest FROM Employees;

AEach row shows the employee's salary and the 2nd lowest salary in their department

BEach row shows the employee's salary and the 2nd highest salary in their department

CEach row shows the employee's salary and the 2nd highest salary in the entire table

DThe query will return an error because NTH_VALUE cannot be used with PARTITION BY

Step-by-Step Solution

Solution:

Step 1: Understand PARTITION BY and ORDER BY
PARTITION BY groups rows by department, ORDER BY salary DESC orders salaries descending within each group.
Step 2: Interpret NTH_VALUE usage
NTH_VALUE(salary, 2) picks the 2nd salary in the ordered partition, which is the 2nd highest salary per department.
Final Answer:
Each row shows the employee's salary and the 2nd highest salary in their department -> Option B
Quick Check:
NTH_VALUE with PARTITION BY = nth value per group [OK]

Quick Trick: PARTITION BY groups rows; ORDER BY defines order inside groups [OK]

Common Mistakes:

Master "Advanced Window Functions" in SQL

9 interactive learning modes - each teaches the same concept differently

Want More Practice?

15+ quiz questions · All difficulty levels · Free

More SQL Quizzes