Above the Average

Some questions need two passes

"Which tools cost more than average?" cannot be answered while reading the rows one at a time, because the average is a fact about all of them. SQL's answer is to let a query contain another query.

The inline form is a subquery: put it in brackets where a value belongs, and it is worked out first.

CREATE TABLE hives (id INTEGER PRIMARY KEY, label TEXT, jars INTEGER);
INSERT INTO hives (label, jars) VALUES
  ('north hedge', 14), ('orchard', 31), ('lane end', 6), ('churchyard', 22);

SELECT round(avg(jars), 2) AS mean_jars FROM hives;

SELECT label, jars
FROM hives
WHERE jars > (SELECT avg(jars) FROM hives)
ORDER BY jars DESC;

mean_jars
---------
    18.25
(1 row)

label      | jars
-----------+-----
orchard    |   31
churchyard |   22
(2 rows)

The subquery returns one row and one column, so it can stand anywhere a single value could. Note that WHERE jars > avg(jars) would not work: an aggregate cannot be tested in a WHERE clause, and the brackets are what give the average its own query to live in.

When the thing you need is itself a summary

The exercise asks for something one step harder: not "above the average deposit" but "above the average member total". The numbers you want the average of do not exist in any table — they have to be computed first, one per member.

A subquery in FROM can do it, and a CTE does the same job with a name on it. WITH name AS ( ... ) goes at the top of the statement, and below it the name behaves exactly like a table.

CREATE TABLE checks (id INTEGER PRIMARY KEY, keeper TEXT, hive TEXT);
INSERT INTO checks (keeper, hive) VALUES
  ('Asha', 'north hedge'), ('Asha', 'orchard'), ('Asha', 'lane end'),
  ('Bruno', 'orchard'),
  ('Cleo', 'lane end'), ('Cleo', 'churchyard');

WITH per_keeper AS (
  SELECT keeper, count(*) AS checks_done
  FROM checks
  GROUP BY keeper
)
SELECT keeper, checks_done
FROM per_keeper
WHERE checks_done > (SELECT avg(checks_done) FROM per_keeper)
ORDER BY checks_done DESC, keeper;

keeper | checks_done
-------+------------
Asha   |           3
(1 row)

per_keeper is read twice in that one statement — once in the FROM and once inside the subquery — which is something a CTE can do and a subquery in FROM cannot. It also gives the intermediate step a name, so the query can be read top to bottom: first work out the checks per keeper, then keep the ones above average.

Two things to remember about a CTE. It belongs to the single statement that defines it, so the semicolon ends its life — it is not a temporary table and nothing is stored. And several can be defined at once, separated by commas, each able to use the ones above it.

Your turn

The editor holds three tables and a query that already does the hard part: the member_totals CTE joins loans to members and tools and produces one row per member, with a loan count and the total deposit value they are holding.

Press Run first. Four rows come back — every member who has borrowed anything — sorted by value.

Add the one missing clause, between FROM member_totals and ORDER BY, so that only the members above the average member total survive. Run it again and two rows should remain.

Then try WHERE value > avg(value) without the subquery, to see the error it produces, and put the working version back.

If something goes wrong

If you get misuse of aggregate function avg(), the average needs its own bracketed subquery rather than sitting directly in the WHERE.

If you get HAVING clause on a non-aggregate query, the new clause is a HAVING and wants to be a WHERE: the outer query groups nothing, so it has no groups to filter.

If you get no such table: member_totals, the subquery is reading a name that does not match the CTE, or the WITH block has been moved below the SELECT.

If you still get four rows, the new clause is not doing any work: check that it sits on the outer query between FROM member_totals and ORDER BY, and that it compares value against the subquery rather than against a number every member already beats.

If you get three rows, the subquery is probably averaging deposit from tools rather than value from the CTE — a smaller threshold, so one member too many survives.

Nothing here can break. All three tables are rebuilt from scratch on every run.