SQL - Advanced Window Functions
You want to find the difference in salary between each employee and the highest salary in their department using window functions. Which query correctly achieves this?
You want to find the difference in salary between each employee and the highest salary in their department using window functions. Which query correctly achieves this?
hard📝 Application Q15 of 15
Step-by-Step Solution
Solution:
Step 1: Use MAX() as window function partitioned by department
MAX(salary) OVER (PARTITION BY department) gives highest salary per department for each row.Step 2: Calculate difference as highest salary minus employee salary
Subtract employee salary from max salary to get positive difference.Step 3: Check options for correct calculation and syntax
SELECT name, department, salary, MAX(salary) OVER (PARTITION BY department) - salary AS diff FROM employees; correctly subtracts salary from max salary per department; SELECT name, department, salary, salary - MAX(salary) OVER (PARTITION BY department) AS diff FROM employees; reverses subtraction; The remaining options misuse GROUP BY or lack window function.Final Answer:
SELECT name, department, salary, MAX(salary) OVER (PARTITION BY department) - salary AS diff FROM employees; -> Option AQuick Check:
MAX() OVER PARTITION BY + subtraction = difference [OK]
Quick Trick: Subtract salary from MAX() OVER partition for difference [OK]
Common Mistakes:
- Reversing subtraction order
- Using GROUP BY instead of window functions
- Omitting PARTITION BY in MAX()
Master "Advanced Window Functions" in SQL
Want More Practice?
15+ quiz questions · All difficulty levels · Free
Free Signup - Practice All QuestionsMore SQL Quizzes