- CAPM
- beta
- covariance
- variance
- WINDOW alias
- rolling window frame
- self-join
- rolling beta sql
- capm beta calculation
- beta vs benchmark
Rolling CAPM Beta in SQL 📐
Monday, 7:40am, the risk desk of a mid-size fund. Overnight, a mega-cap tech name gapped down 4% on earnings. Your head of risk drops by before the open: "How exposed is our book to the market right now? Give me the current beta on our two biggest positions — AAPL and JPM — versus SPY." She does not want a textbook number from a paper; she wants the beta computed from the last 60 trading days, because a stock's sensitivity to the market drifts over time. A single all-history number would be a lie. You need a rolling beta, and you need it in SQL, straight off the price table.
🧠 The mental model: beta is a moving photograph, not a portrait
Think of beta like a runner's recent form, not their career average. A career batting average tells you who they were; their last 10 games tell you who they are today. A stock that behaved defensively three years ago can turn into a high-beta rocket after a business pivot. So risk desks never quote one lifetime beta — they quote a trailing-window beta that slides forward one day at a time, always summarising the recent past only.
That "slide a fixed-length window forward, one row at a time" idea is exactly what a SQL window frame does. The whole lesson lives in one clause: how many rows back does the frame look?
What beta actually measures
Beta is an asset's sensitivity to the market. In symbols, with r_i the stock return and r_m the market return:
β = Cov(r_i, r_m) / Var(r_m)
| Beta | Reading | Typical names |
|---|---|---|
| β > 1 | Amplifies the market — moves more than SPY | high-growth tech |
| β ≈ 1 | Moves roughly with the market | broad index trackers |
| β < 1 | Defensive — moves less than SPY | utilities, staples |
| β < 0 | Counter-cyclical — moves against SPY | gold, some bonds |
📜 CAPM — From Theory to Daily Computation
1964 — William Sharpe published "Capital Asset Prices: A Theory of Market Equilibrium Under Conditions of Risk" in the Journal of Finance. CAPM made a startling claim: the only risk that should earn a return premium is market risk (beta); everything else can be diversified away for free. It won him the 1990 Nobel Prize.
The beta formula β = Cov(r_i, r_m) / Var(r_m) was first estimated in academic papers over 60-month rolling windows of monthly returns — precisely the rolling logic you will implement here, adapted to daily data.
1972 — Fischer Black, Michael Jensen & Myron Scholes tested CAPM empirically (in Jensen, ed., Studies in the Theory of Capital Markets, Praeger) and found the beta–return line flatter than predicted: low-beta stocks beat their CAPM forecast, high-beta stocks lagged. That "low-beta anomaly" is still harvested today by low-volatility funds.
1992 — Fama & French showed beta alone explains surprisingly little of the cross-section of returns, motivating their three-factor model. Yet CAPM remains the first risk model every analyst learns, and rolling beta is still computed for every position in a bank's book, in real time.
Computing β Without COVAR or CORR
The in-browser sql.js engine (like plain SQLite) has no COVAR, CORR, or windowed STDDEV. But β only needs a covariance and a variance, and both drop straight out of windowed sums, which are valid window functions. With x = r_stock and y = r_spy:
β = (n·Σxy − Σx·Σy) / (n·Σy² − (Σy)²)
Every term is a windowed SUM or COUNT, so one shared WINDOW alias drives the whole thing (the sample n−1 factors cancel in the ratio, so you don't even need them):
SQLWINDOW w AS (PARTITION BY ticker ORDER BY trade_date ROWS BETWEEN 59 PRECEDING AND CURRENT ROW)
ROWS BETWEEN 59 PRECEDING AND CURRENT ROW means "today's row plus the 59 before it" — 60 rows in total, i.e. a 60-day trailing window. This is portable to any SQL engine, even ones with no statistical functions at all.
Your Task
The query is fully wired — returns, the self-join to SPY, the covariance/variance ratio, and the ranking are all done. One thing is missing: the window frame. The starter uses a degenerate frame that looks at only the current row, so each "window" holds a single return. Fill in the trailing 60-row frame so w actually rolls over 60 days.
Predict before you run: with a single-row window, the variance of SPY over that one row is 0 — so the denominator is 0. What do you think NULLIF(…, 0) does to every beta, and therefore how many rows will the starter return? Run it, watch it come back empty, then fix the frame and watch AAPL and JPM reappear.