𝗜𝗻𝘁𝗲𝗿𝘃𝗶𝗲𝘄𝗲𝗿:
You have 2 minutes to solve this SQL query.

Find employees whose salary is higher than the average salary of all other departments (excluding their own department).

Assume the table structure:
employees(employee_id, employee_name, department, salary)

𝗠𝗲: Challenge accepted! 💪

SELECT
employee_id,
employee_name,
department,
salary
FROM employees e1
WHERE salary > (
SELECT AVG(salary)
FROM employees e2
WHERE e2.department <> e1.department
);

💡 Explanation:
This query compares each employee's salary against the average salary of all employees outside their own department.

• The outer query processes each employee.
• The correlated subquery calculates the average salary of employees in all other departments.
• Employees whose salary exceeds that average are returned.

This question tests your understanding of:
✅ Correlated Subqueries
✅ Aggregate Functions (AVG)
✅ Conditional Filtering
✅ Cross-group Comparisons

🎯 Expected Output Example
Employee: John | Department: IT | Salary: 95,000
Employee: Sarah | Department: HR | Salary: 82,000


🚀 Alternative Using Common Table Expressions (CTEs)
WITH dept_avg AS (
SELECT
department,
AVG(salary) AS avg_salary
FROM employees
GROUP BY department
)
SELECT
e.employee_id,
e.employee_name,
e.department,
e.salary
FROM employees e
WHERE e.salary > (
SELECT AVG(avg_salary)
FROM dept_avg d
WHERE d.department <> e.department
);

This version first computes department-level averages and then compares each employee's salary with the average of the other departments' averages.

🚀 Tip for SQL Job Seekers:
Interviewers often ask questions that compare data within a group versus outside a group. These problems test your understanding of correlated subqueries and aggregate calculations across multiple levels.

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