Solving tips
- A scalar subquery returns exactly one row and one column, so it can stand in wherever a single value is expected — inside SELECT or on the right of a WHERE comparison.
- The subquery `(SELECT AVG(salary) ...)` runs once for the whole statement, not once per row, because it does not reference the outer query.
- AVG over an integer column returns numeric in PostgreSQL, so cast if you want a clean integer difference.
Find every employee who earns more than the company-wide average salary, and show how far above the average they are.
Schema
CREATE TABLE employees (
id integer PRIMARY KEY,
name text NOT NULL,
department_id integer,
salary integer NOT NULL
);
Sample data — employees:
| id | name | department_id | salary |
|---|
| 1 | Alice | 10 | 90000 |
| 2 | Bob | 10 | 60000 |
| 3 | Carol | 20 | 100000 |
| 4 | Dave | 20 | 55000 |
| 5 | Eve | 30 | 120000 |
| 6 | Frank | 30 | 55000 |
Task
Return the name, the salary, and a column above_avg equal to the employee’s salary minus the company-wide average salary (as an integer), for every employee whose salary is strictly greater than that average.
Order by salary descending, then by name ascending. Expected columns: name, salary, above_avg.
Expected output
The six salaries sum to 480000, so the company average is 80000.
| name | salary | above_avg |
|---|
| Eve | 120000 | 40000 |
| Carol | 100000 | 20000 |
| Alice | 90000 | 10000 |
Approach
The company average is a single number, so compute it with a scalar subquery (SELECT AVG(salary) FROM employees). Because that subquery does not reference the outer row, the planner evaluates it once and reuses the value. Use it twice: once in the WHERE clause to keep only above-average earners, and once in the SELECT list to report the gap. Cast the average to integer so the difference is a clean whole number.
Query
SELECT name,
salary,
salary - CAST((SELECT AVG(salary) FROM employees) AS integer) AS above_avg
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees)
ORDER BY salary DESC, name;
Walkthrough
The inner AVG(salary) over all six rows is 480000 / 6 = 80000. The WHERE filter keeps rows with salary > 80000: Alice (90000), Carol (100000), and Eve (120000); Bob, Dave, and Frank fall at or below the average and drop out. For each surviving row, above_avg is salary - 80000, giving 10000, 20000, and 40000. Ordering by salary DESC puts Eve first, then Carol, then Alice, matching the expected output.
Complexity & notes
The average requires one full scan of employees, and the outer filter another; both are linear in the row count, and a well-optimized engine caches the non-correlated scalar result. Pitfalls: AVG of an integer column returns numeric in PostgreSQL (e.g. 80000.0000000000000000), so cast it if you want an integer result — otherwise above_avg prints with a long decimal tail. If the table is empty, the scalar subquery yields NULL, and salary > NULL is never true, so the result is simply empty. Comparing against a scalar subquery that accidentally returns more than one row raises a runtime error, so make sure aggregation guarantees a single value.