Detailed explanation
To find employees earning more than their department average, calculate the average salary separately for each department, then keep employees whose salary is strictly greater than that average. The comparison must use the employee’s own department, not the company-wide average. The employee’s salary is included in the department average unless the question explicitly asks for the average of other employees. This problem tests whether you can combine row-level details with group-level calculations without losing individual employee records.
A window function is a clear solution: SELECT name, salary FROM (SELECT name, salary, AVG(salary) OVER (PARTITION BY dept_id) AS avg_sal FROM employees) t WHERE salary > avg_sal; PARTITION BY dept_id divides rows into department-specific groups for the calculation. Unlike GROUP BY, the window function preserves each employee row and attaches the corresponding average to it. The outer query then filters these enriched rows. This extra query level is needed because window functions are evaluated logically after WHERE, so their results cannot be filtered directly in the same query block’s WHERE clause.
Another approach calculates one average per department and joins those results back to employees: SELECT e.name, e.salary FROM employees e JOIN (SELECT dept_id, AVG(salary) AS avg_sal FROM employees GROUP BY dept_id) d ON e.dept_id = d.dept_id WHERE e.salary > d.avg_sal; A correlated subquery also works: SELECT e.name, e.salary FROM employees e WHERE e.salary > (SELECT AVG(e2.salary) FROM employees e2 WHERE e2.dept_id = e.dept_id); These alternatives express the same comparison when department IDs are non-NULL. Their execution plans depend on the database optimizer; do not assume a correlated subquery necessarily performs a separate full scan for every employee.
Interviewers also look for edge-case awareness. AVG ignores NULL salaries, and employees with NULL salaries fail the comparison because its result is unknown. A department with only one non-NULL salary returns no employee above its average. Window partitioning groups NULL department IDs together, whereas ordinary equality joins and correlations do not match NULL IDs. Confirm the intended treatment of unassigned employees and check your database’s AVG return type if integer salaries could lose fractional precision.
Key points to remember
- Partition or group by dept_id to calculate a department-specific average.
- Use a window function to retain employee rows alongside the aggregate.
- Filter window results in an outer query or CTE.
- Use salary > avg_sal, not >=, for strictly above-average salaries.
- Grouped joins and correlated subqueries are valid alternatives; compare execution plans.
Common mistakes candidates make
- Comparing salaries against the company-wide average instead of each department’s average.
- Trying to reference the window average in the same query block’s WHERE clause.
- Using GROUP BY alone and losing the individual employee rows needed in the output.
Likely follow-up questions
Why not add ORDER BY inside OVER?
The requirement is a full-department average, so PARTITION BY is sufficient. Adding ORDER BY can introduce a default window frame that produces a running average rather than the full-department average.
How would you calculate the average salary of other employees in the department?
For employees with non-NULL salaries, calculate window SUM(salary) and COUNT(salary), then use (department_sum - salary) / (department_count - 1). Use NULLIF on the denominator to avoid division by zero and an appropriate numeric cast to preserve fractional results.
Which approach performs best on a large table?
There is no universally fastest approach; inspect the execution plan using realistic data. An index beginning with dept_id may help some plans, but reading most employees can still make a scan efficient.
Interview tip: Present the window-function query first, explain why it needs an outer filter, and briefly mention NULL handling and an alternative approach.