SQL - Window Functions Fundamentals
You want to display each employee's salary rank within their department along with the department's average salary. Which query correctly uses window functions to achieve this?
You want to display each employee's salary rank within their department along with the department's average salary. Which query correctly uses window functions to achieve this?
hard📝 Application Q8 of 15
Step-by-Step Solution
Solution:
Step 1: Understand requirements
We need salary rank per department and average salary per department shown for each employee.Step 2: Analyze options
SELECT employee_id, department, salary, RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS salary_rank, AVG(salary) OVER (PARTITION BY department) AS avg_department_salary FROM employees; uses RANK() and AVG() both as window functions partitioned by department, which is correct.
SELECT employee_id, department, salary, RANK() OVER (ORDER BY salary DESC) AS salary_rank, AVG(salary) FROM employees GROUP BY department; uses RANK() without partitioning and AVG() as aggregate without GROUP BY, incorrect.
SELECT employee_id, department, salary, RANK() OVER (PARTITION BY department ORDER BY salary) AS salary_rank, AVG(salary) FROM employees; uses AVG() as aggregate without GROUP BY, incorrect.
SELECT employee_id, department, salary, RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS salary_rank, AVG(salary) GROUP BY department FROM employees; mixes window function and GROUP BY incorrectly.Final Answer:
Option A -> Option AQuick Check:
Use window functions for per-row aggregates without GROUP BY [OK]
Quick Trick: Use window functions for per-row group aggregates [OK]
Common Mistakes:
- Using aggregate functions without GROUP BY
- Not partitioning window functions properly
- Mixing GROUP BY with window functions incorrectly
Master "Window Functions Fundamentals" in SQL
Want More Practice?
15+ quiz questions · All difficulty levels · Free
Free Signup - Practice All QuestionsMore SQL Quizzes