𝗜𝗻𝘁𝗲𝗿𝘃𝗶𝗲𝘄𝗲𝗿:
You have 2 minutes to solve this SQL query.
Find the departments where the average salary is greater than the company's overall average salary.

𝗠𝗲: Challenge accepted! 💪
SELECT
department,
AVG(salary) AS average_salary
FROM employees
GROUP BY department
HAVING AVG(salary) > (
SELECT AVG(salary)
FROM employees
);

💡 Explanation:
The query compares each department's average salary with the company's overall average salary.

• GROUP BY department calculates the average salary for each department.
• The subquery computes the overall average salary across all employees.
• HAVING filters only those departments whose average salary exceeds the company average.

This question tests your understanding of:
✅ GROUP BY
✅ HAVING
✅ Aggregate Functions AVG
✅ Subqueries

🎯 Expected Output Example
Department: Average Salary
IT: 88,500
Finance: 84,000

HR is excluded because its average salary is below the company average.

🚀 Alternative Using a Common Table Expression CTE
WITH company_avg AS (
SELECT AVG(salary) AS avg_salary
FROM employees
)
SELECT
department,
AVG(salary) AS average_salary
FROM employees, company_avg
GROUP BY department, company_avg.avg_salary
HAVING AVG(salary) > company_avg.avg_salary;

Using a CTE can improve readability, especially when the same calculated value is reused in larger queries.

🚀 Tip for SQL Job Seekers:
Interviewers often ask questions that compare group-level aggregates with overall aggregates. Master the use of HAVING with subqueries—it’s a key SQL pattern.

❤️ React with ❤️ for more SQL interview challenges!