Above Average Salaries — Scalar Subqueries in SQL

The problem

Helix Systems' compensation team wants to flag every salary record that exceeds the company-wide average salary.

Write a query to return the employee ID and salary amount for every above-average salary record.

Assumptions:

The salaries table contains every salary record (current and historical).
The company-wide average is computed across every row in the salaries table.
An employee with multiple salary records may have some above and some below the average.

Output:

One row per above-average salary record, with columns employee_id and amount.

Schema · hr 4 tables

departments

id integer

name text

location text

budget numeric

salaries

id integer

employee_id integer

amount numeric

effective_date date

end_date? date

employees

id integer

name text

email text

department_id integer

manager_id? integer

hire_date date

title text

is_active boolean

job_history

id integer

employee_id integer

title text

department_id integer

start_date date

end_date? date

Check answerShift Ctrl ↵

Run previews · Check grades

Write a query, then run it to see results here.

Worked solution Try it yourself first

Solution query

SELECT
  employee_id,
  amount
FROM
  salaries
WHERE
  amount > (
    SELECT
      AVG(amount)
    FROM
      salaries
  )

The shape

Same structural pattern as the product-average filter, applied to Helix's salary records. (SELECT AVG(amount) FROM salaries) resolves to the company-wide average, and the outer WHERE keeps only the rows whose amount is strictly above that number.

Clause by clause

FROM salaries is the source: every salary record, including historical ones and multiple records per employee.
WHERE amount > (SELECT AVG(amount) FROM salaries) filters row by row. The subquery returns a single number — the average across every row in salaries. Each outer row's amount is compared against that number; rows at or below the average drop.
SELECT employee_id, amount returns the two identifying fields per qualifying record. An employee can appear multiple times in the result if more than one of their salary records sits above the company average — and disappear from rows where an earlier salary was below it. The filter operates per record, not per employee.

Why this and not a literal

A hardcoded threshold would go stale every time a new salary is recorded. The subquery recomputes the average on every run, so "above average" tracks the table as it changes — which is what the compensation team's flagging report needs.

You practiced the same scalar-subquery-in-WHERE shape against a different table. The pattern doesn't care about the domain — wherever the threshold is "the average / max / min of the column being filtered," the scalar subquery is the natural expression.

Return the employee ID and salary amount for every above-average salary record

The shape

Clause by clause

Why this and not a literal

Reading explains SQL. Writing it, over and over with instant feedback, is what makes you fluent.