Add a new column with the average salary by department.

To add a new column with the average salary by department, use a SQL window function with AVG() and PARTITION BY. This calculates the average salary for each department while preserving all individual employee rows, unlike a GROUP BY clause.

Mechanics and Why

The OVER() clause defines the "window" or set of rows on which the aggregate function operates. PARTITION BY department_id divides your dataset into distinct groups based on the department. The AVG(salary) function then computes the average salary independently within each of these partitions. This approach is powerful because it allows you to combine aggregate calculations with detailed row-level data in a single query, avoiding the need for self-joins or subqueries that might be less efficient or harder to read.

Example and Performance

SELECT employee_id, name, salary,
       AVG(salary) OVER (PARTITION BY department_id) AS dept_avg_salary,
       AVG(salary) OVER () AS overall_avg_salary -- For comparison
FROM employees;

This query efficiently computes the departmental average for every employee. From a performance perspective, window functions typically involve an internal sort or hash operation based on the PARTITION BY columns. In distributed systems like Spark, this translates to a shuffle operation, which can be resource-intensive. Modern data warehouses like Snowflake optimize these operations using techniques like micro-partitions and clustering keys to minimize data movement and improve query execution.

In the interview, also mention…

Contrast this with using a GROUP BY clause, which would collapse rows and only return one row per department. Explain that while a GROUP BY in a CTE or subquery followed by a JOIN could achieve a similar result, window functions are generally more concise, often more performant (due to a single scan of the data), and are the idiomatic solution for this type of "analytic" query.