SQL Junior (0–2 yrs) Coding Very frequent SQL subquery aggregation AVG filtering

Write a query to find all employees who earn more than the average salary.

sql-jun-002

Your answer

Answer as you would in a real interview — explain your thinking, not just the conclusion.

Model answer

I use a subquery in the WHERE clause that calculates the average salary across the whole table, then filter rows where the individual salary exceeds it. The subquery runs once and the outer query compares each row against that scalar value. An alternative is to compute the average in a CTE and cross join it to avoid repeating the subquery, which is clearer in complex queries. I always alias the subquery result to make the intent obvious, and I confirm the column types match to avoid implicit casting issues.

Code example

-- Subquery approach
SELECT id, name, salary
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees)
ORDER BY salary DESC;

-- CTE approach (same result, easier to extend)
WITH avg_sal AS (
    SELECT AVG(salary) AS avg_salary FROM employees
)
SELECT e.id, e.name, e.salary, a.avg_salary
FROM employees e
CROSS JOIN avg_sal a
WHERE e.salary > a.avg_salary
ORDER BY e.salary DESC;

Follow-up

How would you rewrite this using a window function? What is the advantage of AVG() OVER () compared to a correlated subquery?