You want to find the 4th highest salary per department but only for departments with at least 5 employees. Which query correctly uses NTH_VALUE and filters departments accordingly?
hard📝 Application Q8 of 15
SQL - Advanced Window Functions
You want to find the 4th highest salary per department but only for departments with at least 5 employees. Which query correctly uses NTH_VALUE and filters departments accordingly?
ASELECT department, NTH_VALUE(salary, 4) OVER (PARTITION BY department ORDER BY salary DESC) AS fourth_salary FROM Employees GROUP BY department HAVING COUNT(*) >= 5;
BSELECT department, NTH_VALUE(salary, 4) OVER (PARTITION BY department ORDER BY salary DESC) AS fourth_salary FROM Employees HAVING COUNT(*) >= 5;
CSELECT department, NTH_VALUE(salary, 4) OVER (PARTITION BY department ORDER BY salary DESC) AS fourth_salary FROM Employees WHERE COUNT(*) >= 5;
DSELECT department, NTH_VALUE(salary, 4) OVER (PARTITION BY department ORDER BY salary DESC) AS fourth_salary FROM Employees WHERE department IN (SELECT department FROM Employees GROUP BY department HAVING COUNT(*) >= 5);
Step-by-Step Solution
Solution:
Step 1: Filter departments with at least 5 employees
Use a subquery with GROUP BY and HAVING to get departments meeting the count condition.
Step 2: Apply NTH_VALUE per department
Use PARTITION BY department and ORDER BY salary DESC to get 4th highest salary per department.
Step 3: Combine filtering and window function
Filter rows in main query using WHERE with subquery to restrict departments before applying NTH_VALUE.
Final Answer:
SELECT department, NTH_VALUE(salary, 4) OVER (PARTITION BY department ORDER BY salary DESC) AS fourth_salary FROM Employees WHERE department IN (SELECT department FROM Employees GROUP BY department HAVING COUNT(*) >= 5); -> Option D
Quick Check:
Filter before window function with subquery [OK]
Quick Trick:Filter groups with subquery before window function [OK]
Common Mistakes:
Using HAVING without GROUP BY in main query
Trying to filter with WHERE COUNT(*)
Not filtering departments before window function
Master "Advanced Window Functions" in SQL
9 interactive learning modes - each teaches the same concept differently
