🧠 Advance SQL Interview Question ⚡

📊 Find the department with the highest total salary expense

Table: Employees

Columns:

employee_id , employee_name , department_id , salary

🔍 Query:

WITH dept_salary AS (
SELECT department_id,
SUM(salary) AS total_salary
FROM Employees
GROUP BY department_id
)

SELECT department_id,
total_salary
FROM dept_salary
WHERE total_salary = (
SELECT MAX(total_salary)
FROM dept_salary
);

🎯 Why this question matters:

✅ Tests CTE + aggregation concepts
✅ Evaluates nested subquery understanding

🚀 Pro Tip:

Using a CTE first makes complex aggregate queries much cleaner and easier to debug.

🔥 React ❤️ for more advanced SQL interview questions 🚀