CTEs · Structured SQL

CTEs - Assemble a Query Like a Pipeline 🧩

Scene: 4:45pm on the desk. Your PM leans over: "Rank our five names by annualised return, top three, before the close." You know the recipe - previous close, log return, average, annualise, rank - but cramming all five steps into one nested query is a wall of parentheses nobody can read or debug. A senior quant glances at your screen and says: "Don't nest it. Chain it. Use a WITH."

A CTE (Common Table Expression, the WITH clause) lets you name each step of a calculation and feed it into the next - like connecting stages of a data pipeline. Instead of one giant nested subquery, you write a readable ladder where every rung has a name.

🧠 The mental model: a relay race

Think of a CTE chain as a relay race. Each runner (CTE) does one leg, then hands the baton to the next:

• daily_returns grabs each day's close and the previous day's close (the baton it passes on).
• log_returns takes that baton and turns two consecutive closes into one daily return.
• avg_returns takes those and folds a ticker's ~520 daily returns into one annualised number.
• The final SELECT sorts the finishers and takes the podium (top 3).

🪟 New syntax, given to you: the first leg uses LAG(close_price) OVER (PARTITION BY ticker ORDER BY trade_date) - a window function that simply reads the previous row's close for the same ticker. Don't worry about writing window functions yet; that is the whole job of the advanced SQL track. Today it is pre-written scaffolding - your focus is the CTE chain, and the one clause inside it that you will complete.

No runner does everything; each does one job and passes a clean, named result forward. That is exactly what WITH buys you - and why quants at every desk reach for it.

CTE syntax

WITH step_1 AS (
    SELECT ticker, AVG(close_price) AS avg_close
    FROM   equity_prices
    GROUP  BY ticker
),
step_2 AS (            -- step_2 can read FROM step_1, like the next relay leg
    SELECT * FROM step_1 WHERE avg_close > 200
)
SELECT * FROM step_2 ORDER BY avg_close DESC;

Two rules make the whole thing click:

The step that matters most: the fold

The third leg, avg_returns, is where the magic - and the classic bug - lives. It uses AVG(log_ret), an aggregate: a function that squeezes many rows into one. But into one per what? That is decided entirely by GROUP BY.

• With GROUP BY ticker → SQL makes one bucket per ticker, averages inside each → 5 rows (one per name). Correct.
• Without it → SQL makes a single bucket of every row from all five tickers, averages the whole soup → 1 meaningless row. This is the #1 CTE mistake juniors ship.

A useful tell lives in the trading_days column (a plain COUNT(*)). It answers "how many daily returns went into this average?" - so it is a built-in sanity check on your grouping:

Reading a COUNT to audit your own aggregation is a habit that separates analysts who trust their numbers from those who ship silent bugs. If the return your query hands the PM was averaged over 2595 "days", it is nonsense - five companies blended into one imaginary stock.

Why this ranking is a real desk task

The five-leg chain you are finishing is a miniature momentum screen - the same shape hedge funds run every morning to spot which names are trending. daily_returns and log_returns build the feature; avg_returns reduces it to a rankable score; the final SELECT picks winners. Swap the ORDER BY and you have a losers screen for mean-reversion. The plumbing you write today generalises to almost every signal a quant researches.

Your Task

The starter is a finished three-leg CTE chain - except the fold in avg_returns is missing its GROUP BY. Run it as-is first: it executes without error, but watch what trading_days shows.

Predict before you run: each ticker has ~520 trading days. If you forget GROUP BY ticker, all five tickers pour into one bucket - so what will the single trading_days value be, roughly, and how many rows come back? Guess (≈5 × 520, and 1 row), then run and see the 2595-ish blob appear. Now add the one clause that splits the soup back into five clean per-ticker returns - and the ranking becomes real.