How can you combine NTH_VALUE with CASE to show the 3rd highest salary per department only if it is above 50000, otherwise show NULL?
hard📝 Application Q9 of 15
SQL - Advanced Window Functions
How can you combine NTH_VALUE with CASE to show the 3rd highest salary per department only if it is above 50000, otherwise show NULL?
ASELECT department, NTH_VALUE(salary, 3) OVER (PARTITION BY department ORDER BY salary DESC) AS third_salary FROM Employees WHERE salary > 50000;
BSELECT department, NTH_VALUE(CASE WHEN salary > 50000 THEN salary ELSE NULL END, 3) OVER (PARTITION BY department ORDER BY salary DESC) AS third_salary FROM Employees;
CSELECT department, NTH_VALUE(salary, 3) OVER (PARTITION BY department ORDER BY salary DESC) WHERE salary > 50000 AS third_salary FROM Employees;
DSELECT department, CASE WHEN NTH_VALUE(salary, 3) OVER (PARTITION BY department ORDER BY salary DESC) > 50000 THEN NTH_VALUE(salary, 3) OVER (PARTITION BY department ORDER BY salary DESC) ELSE NULL END AS third_salary FROM Employees;
Step-by-Step Solution
Solution:
Step 1: Calculate 3rd highest salary per department
Use NTH_VALUE with PARTITION BY and ORDER BY to get the 3rd highest salary.
Step 2: Use CASE to conditionally show value
Wrap NTH_VALUE in CASE to check if value is above 50000; else return NULL.
Step 3: Validate other options
SELECT department, NTH_VALUE(CASE WHEN salary > 50000 THEN salary ELSE NULL END, 3) OVER (PARTITION BY department ORDER BY salary DESC) AS third_salary FROM Employees; applies CASE inside NTH_VALUE which affects ordering; the other options misuse WHERE clause.
Final Answer:
SELECT department, CASE WHEN NTH_VALUE(salary, 3) OVER (PARTITION BY department ORDER BY salary DESC) > 50000 THEN NTH_VALUE(salary, 3) OVER (PARTITION BY department ORDER BY salary DESC) ELSE NULL END AS third_salary FROM Employees; -> Option D
Quick Check:
Use CASE outside NTH_VALUE for conditional display [OK]
Quick Trick:Wrap NTH_VALUE in CASE for conditional output [OK]
Common Mistakes:
Putting CASE inside NTH_VALUE affecting order
Using WHERE inside SELECT incorrectly
Filtering rows instead of conditional display
Master "Advanced Window Functions" in SQL
9 interactive learning modes - each teaches the same concept differently
