Subqueries: Find Above-Average Stocks

Subqueries - A Query Inside Your Query 🔍

Scene: Monday, 7:40am on the equities desk. Your PM drops a one-liner in Slack: "Which of our names are trading rich versus the book? Give me only the ones above the average - I don't care about the laggards." You have five tickers and two years of closes. The trap is thinking you need two queries: one to compute the average, then a second where you paste that number in by hand. Do that and you will be re-pasting it every morning as prices move. There is a better way - let SQL compute the benchmark inside the same query.

🧠 The mental model: the moving finish line

Think of a race where the finish line is not fixed - it sits at the average finish time of all runners. You cannot know where it is until every runner is in. A subquery is exactly that: a little SELECT that runs first, hands its answer back to the outer query, and lets the outer query judge each row against it. You never type the benchmark number yourself; the database re-computes it every run. Prices moved overnight? The finish line moves with them, automatically.

The two flavours you will meet

Today is all about the scalar kind - the workhorse. A scalar subquery is any SELECT that returns a single cell, so SQL can drop it straight into a comparison as if you had typed a number:

HAVING AVG(close_price) > (SELECT AVG(close_price) FROM equity_prices)
--                         └──────── one number: the whole-market average ────────┘

The inner SELECT collapses every close price across all five tickers into a single grand average. The outer query then keeps only the groups whose own average clears that bar.

WHERE vs HAVING - why the subquery lives in HAVING here

You are filtering on AVG(close_price), an aggregate. WHERE is evaluated before rows are grouped, so it cannot see a group's average - only HAVING, which runs after GROUP BY, can. That is why the benchmark comparison belongs in HAVING, not WHERE.

Your Task

The starter already builds the right shape: it groups by ticker, computes each ticker's avg_close, and even prints an above_market_by column (how far each ticker sits above or below the market). But its HAVING clause is a placeholder - HAVING AVG(close_price) > 0 - which is always true, so every ticker survives, including the cheap laggards your PM told you to drop.

👉 Your job: replace that 0 with a scalar subquery that computes the overall average close across every ticker, so the filter keeps only the winners.

Predict before you run: the starter returns all 5 tickers. The market average is a weighted blend of the five ticker averages - so it must sit strictly between the cheapest and the priciest name. Once your subquery is in place, can the result ever still show all 5 tickers? Think it through, then run and count the rows.