The Monday-Morning Leaderboard 🏗️

Scene: 7:40 a.m. on the equities desk. Your PM drops a Slack message before the open: "Rank our five names by trailing return, and show me the risk that came with it — I want the Sharpe next to every number." You have five tickers, two years of daily prices, and about four minutes. Reaching for a spreadsheet means 2,600 rows of copy-paste and a formula error waiting to happen. The professional move is a single SQL pipeline that does the whole thing in one shot.

🧠 The mental model: a factory assembly line

Think of a multi-CTE query as a factory line, where each station does one job and hands its output to the next:

raw prices  →  [ daily_returns ]  →  [ ticker_stats ]  →  [ ranked ]  →  leaderboard
                 one row/day          one row/ticker       + a rank

A CTE (WITH name AS (...)) is just a named temporary table that lives for one query. Chaining them is the SQL twin of a pandas method chain (df.pct_change().groupby(...).agg(...).rank()) — but every station stays readable, testable, and named. Quants build queries this way because you can run the pipeline up to any station and eyeball that stage before moving on.

The three stations

The pivot of the whole pipeline is station 2. Stations 1 and 3 keep the grain the same (one row in, one row out); station 2 is where 2,600 daily rows must collapse to just 5 summary rows — one per ticker. That collapse is the concept this lesson is about.

The clause that does the collapsing

When a SELECT mixes a plain column with aggregates, SQL needs to know: aggregate over what groups? That is exactly what GROUP BY answers.

 SQL
SELECT ticker, AVG(log_ret)     -- plain column + aggregate
FROM daily_returns
GROUP BY ticker                 -- → one AVG per ticker

Omit the GROUP BY and SQLite does not error — it quietly treats the whole table as one giant group, returning a single row and picking an arbitrary ticker to sit beside the numbers. That silent collapse is the classic trap, and spotting it is the skill here.

Annualising, the desk convention

The stats use the 252-trading-day year (roughly the number of NYSE sessions):

• ›Annualised return ≈ r̄ × 252
• ›Annualised vol ≈ σ × √252
• ›Sharpe ratio = (r̄ × 252) ⁄ (σ × √252)

where r̄ is the mean daily log return and σ its standard deviation. This form assumes a zero risk-free rate for simplicity; the full Sharpe ratio subtracts the risk-free rate from the return first — which, with Fed Funds near 5% in the data, materially lowers it.

Your Task

The pipeline is fully wired except for one missing clause in ticker_stats. Fill in the -- 👉 YOUR TURN line so the aggregates are computed once per ticker.

Predict before you run: the final SELECT keeps return_rank <= 3. If you run the starter with the clause still missing, how many rows come back — and why does that number, not an error, tell you the grouping is wrong? (Hint: count the groups the engine thinks it has.)