Data Analytics on Telegram: “This asks: Which employees earn more than the av…”

This asks:

Which employees earn more than the average salary of their own department?

The inner query depends on the current employee's department. This is more advanced than a basic subquery.

2️⃣4️⃣ Why Correlated Subqueries Matter

Suppose: IT Average salary = ₹80,000, HR Average salary = ₹60,000

An employee earning ₹75,000: Could be below IT average, Could be above HR average

So comparing everyone to the overall company average isn't enough. A correlated subquery allows you to compare each employee to the relevant group.

2️⃣5️⃣ Subquery vs JOIN

Sometimes the same problem can be solved using either a subquery or a JOIN.

For example, finding customers with orders can be done with: WHERE EXISTS (...) or: JOIN Orders ...

Neither is universally better.

The right choice depends on: What result you need, Whether duplicates matter, Query readability, Database optimizer, Data structure

Focus on understanding the logic rather than memorizing one preferred method.

2️⃣6️⃣ A Very Common Interview Problem

Question:

Find employees earning more than their department's average salary.

A correlated subquery solution:

SELECT
    e.Name,
    e.Department,
    e.Salary
FROM Employees e
WHERE e.Salary > (
    SELECT AVG(e2.Salary)
    FROM Employees e2
    WHERE e2.Department = e.Department
);

This is an excellent interview question because it tests: Subqueries, Aggregation, Correlation, Business logic

2️⃣7️⃣ Another Interview Problem

Question:

Find customers whose total sales are greater than ₹1,00,000.

Using a derived table:

SELECT
    Customer_ID,
    Total_Sales
FROM (
    SELECT
        Customer_ID,
        SUM(Sales) AS Total_Sales
    FROM Orders
    GROUP BY Customer_ID
) AS Customer_Sales
WHERE Total_Sales > 100000;

Or using a CTE:

WITH Customer_Sales AS (
    SELECT
        Customer_ID,
        SUM(Sales) AS Total_Sales
    FROM Orders
    GROUP BY Customer_ID
)
SELECT
    Customer_ID,
    Total_Sales
FROM Customer_Sales
WHERE Total_Sales > 100000;

Both approaches produce the same analytical idea.

🧪 Practical Interview Challenge

Suppose you have Employees table

Q1. Find employees earning above the overall average.

SELECT
    Name,
    Salary
FROM Employees
WHERE Salary > (
    SELECT AVG(Salary)
    FROM Employees
);

Q2. Find employees earning above their department average.

SELECT
    e.Name,
    e.Department,
    e.Salary
FROM Employees e
WHERE e.Salary > (
    SELECT AVG(e2.Salary)
    FROM Employees e2
    WHERE e2.Department = e.Department
);

Q3. Create a CTE containing average salary by department.

WITH Department_Salary AS (
    SELECT
        Department,
        AVG(Salary) AS Average_Salary
    FROM Employees
    GROUP BY Department
)
SELECT *
FROM Department_Salary;

Q4. Find departments whose average salary exceeds ₹70,000.

WITH Department_Salary AS (
    SELECT
        Department,
        AVG(Salary) AS Average_Salary
    FROM Employees
    GROUP BY Department
)

SELECT
    Department,
    Average_Salary
FROM Department_Salary
WHERE Average_Salary > 70000;

Q5. Find customers who have placed at least one order.

SELECT
    c.Customer_ID,
    c.Customer_Name
FROM Customers c
WHERE EXISTS (
    SELECT 1
    FROM Orders o
    WHERE o.Customer_ID = c.Customer_ID
);

🏆 Double Tap ❤️ For More