Find average salary for each manager – Assume a table with manager_id and employee_salary

To find the average salary for each manager, you should use the AVG() aggregate function combined with a GROUP BY clause on the manager_id. This groups all employees under the same manager and then calculates their average salary.

SELECT
    manager_id,
    AVG(employee_salary) AS avg_salary
FROM
    employees
GROUP BY
    manager_id;

Mechanics and Considerations

The GROUP BY manager_id clause is fundamental here; it partitions the dataset into distinct groups, one for each unique manager_id. The AVG(employee_salary) function then computes the arithmetic mean of salaries within each of these groups.

Handling NULLs: By default, GROUP BY treats NULL values in manager_id as a single group. If employees without an assigned manager should be excluded from the aggregation, add a WHERE manager_id IS NOT NULL clause before* the GROUP BY.
Filtering Aggregated Results: If you need to filter the results based on the average salary itself (e.g., managers with an average salary above a certain threshold), use the HAVING clause. HAVING filters after aggregation, whereas WHERE filters before* aggregation. For example: HAVING AVG(employee_salary) > 75000.

Performance and Scaling

For large employees tables, query performance is critical.

* Indexing: An index on manager_id is highly recommended. It allows the database to quickly locate and group records, significantly speeding up the GROUP BY operation by avoiding full table scans.
* Data Distribution: In distributed data warehouses or processing engines (e.g., Snowflake, Spark), how data is physically organized can impact performance. If employees data is clustered (like Snowflake's clustering keys) or partitioned by manager_id, it can minimize data shuffling (in Spark) or improve scan efficiency (in Snowflake micro-partitions), making the aggregation much faster.

In the interview, also mention: The distinction between WHERE and HAVING clauses and their execution order is a common interview topic.