SQL - Window Functions Fundamentals
You need to display each employee's salary alongside the average salary of their department, preserving all individual salary rows. Which SQL query accomplishes this?
You need to display each employee's salary alongside the average salary of their department, preserving all individual salary rows. Which SQL query accomplishes this?
hard📝 Application Q8 of 15
Step-by-Step Solution
Solution:
Step 1: Identify requirement
Show each employee's salary and the department average without collapsing rows.Step 2: Analyze options
SELECT employee_id, salary, AVG(salary) OVER (PARTITION BY department_id) AS dept_avg FROM employees; uses a window function with PARTITION BY department_id, preserving all rows and adding the average per department.Step 3: Why others are wrong
Options B and C use GROUP BY, which aggregates rows and loses individual salaries. SELECT employee_id, salary, AVG(salary) FROM employees; lacks grouping or window function.Final Answer:
SELECT employee_id, salary, AVG(salary) OVER (PARTITION BY department_id) AS dept_avg FROM employees; meets the requirement.Quick Check:
Window functions preserve rows; GROUP BY aggregates. [OK]
Quick Trick: Use window functions to keep rows and add aggregates. [OK]
Common Mistakes:
- Using GROUP BY and losing individual rows.
- Not partitioning window function by department.
- Omitting window function syntax.
Master "Window Functions Fundamentals" in SQL
Want More Practice?
15+ quiz questions · All difficulty levels · Free
Free Signup - Practice All QuestionsMore SQL Quizzes