Write a SQL query to find departments with more than 10 employees.

To find departments with more than 10 employees, you'd use a SQL query that groups employees by department and then filters those groups based on the count.

SELECT department_id, COUNT(*) AS emp_count
FROM employees
GROUP BY department_id
HAVING COUNT(*) > 10;

This query first segments the employees table into groups using GROUP BY department_id. For each group, COUNT() calculates the total number of employees. The HAVING clause then filters these aggregated groups, retaining only those where the emp_count exceeds 10. It's crucial to distinguish HAVING from WHERE: WHERE filters individual rows before aggregation, while HAVING filters groups after* aggregation.

For a more complete view, you'd typically join this result with a departments table on department_id to retrieve the department_name. This would involve a JOIN clause before the GROUP BY. To ensure performance on large datasets, an index on employees.department_id is critical as it speeds up the GROUP BY operation. In distributed systems like Apache Spark, GROUP BY triggers a shuffle, which can be expensive; optimizing partition keys or using techniques like pre-aggregation can mitigate this. For frequently accessed results, especially for dashboards, consider creating a materialized view (e.g., in Snowflake or using dbt models) to pre-compute and store these aggregations, avoiding repeated expensive queries.

In the interview, also mention: The performance implications of GROUP BY on large tables and the clear distinction between WHERE and HAVING for filtering.