Data Analytics on Telegram: “𝗜𝗻𝘁𝗲𝗿𝘃𝗶𝗲𝘄𝗲𝗿: You have 2 minutes to so…”

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

Find employees who earn more than the average salary of their own department.

𝗠𝗲: Challenge accepted! 💪

SELECT
    employee_id,
    employee_name,
    department,
    salary
FROM employees e
WHERE salary > (
    SELECT AVG(salary)
    FROM employees
    WHERE department = e.department
);

💡 Explanation:

The query uses a correlated subquery to calculate the average salary for each employee's department.

• The outer query iterates through each employee.
• The inner query calculates the average salary of that employee's department.
• If an employee's salary is greater than their department's average, they're included in the result.

This is a classic SQL interview question that tests your understanding of:
✅ Correlated Subqueries
✅ Aggregate Functions (AVG)
✅ Filtering with WHERE

🎯 Expected Output Example

+----------+------------+--------+
| Employee | Department | Salary |
+----------+------------+--------+
| John     | IT         | 90,000 |
| Sarah    | HR         | 70,000 |
| David    | Finance    | 85,000 |
+----------+------------+--------+

(Only employees earning above their department's average salary.)

🚀 Correlated subqueries are asked frequently in interviews. Learn when to use them—and also know how to rewrite them using window functions for better performance on large datasets.

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