SQL Window Functions: 20-Day Moving Average & Volatility

Window Functions - Analytics Without Losing Rows 🪟

Scene: 7:40am on a US equities desk. The head of research drops a Bloomberg export on your screen - two years of daily closes for AAPL, MSFT, JPM, GS and SPY - and one instruction: "Give me each name's 20-day moving average and its annualised vol, as of the last close, before the 8am huddle." In Excel this is 500 rows of dragged formulas per ticker and a copy-paste nightmare. In SQL it is one query - if you know the one clause that turns an ordinary aggregate into a rolling one.

That clause is the window frame, and it is what this lesson is about.

🧠 The mental model: a sliding 20-day ruler

Picture a transparent ruler exactly 20 trading days wide laid over one ticker's price history, sorted oldest-to-newest. For each row it covers "today and the 19 days before it." Slide it forward one day and it drops the oldest day, adds today, and you read off a fresh average. GROUP BY would melt those 500 rows into a single number and throw the daily detail away. A window function keeps every row and writes the rolling answer next to each one - the ruler slides, the rows stay.

Three dials control the ruler, all living inside OVER( … ):

Miss the frame dial and SQL falls back to a default that is almost never what a quant wants (see below).

---

📜 How SQL Got Window Functions - And Why Finance Drove It

Edgar Codd defined the relational model at IBM in 1970; SQL - first called SEQUEL - was built there by Donald Chamberlin and Raymond Boyce in 1974 for set-based questions about groups of rows (Codd himself later criticised how far SQL drifted from his theory). Great for inventory, broken for time-series. To get a 20-day moving average in the 1980s you wrote a correlated subquery that re-ran once per row: 250 days × 500 tickers = 125,000 nested queries, hours of runtime. Many quants fled to Excel or FORTRAN.

SQL:2003 finalised the OVER(PARTITION BY … ORDER BY … ROWS BETWEEN …) syntax, pushed hard by Oracle, DB2 and Teradata - whose banking clients (JPMorgan, Goldman Sachs) needed temporal analytics at scale. Every time a Bloomberg Terminal draws a 50-day moving average, the backend runs a query structurally identical to the one you are about to write, across millions of tickers at once.

---

ROWS vs the default frame - the trap

The maths you are wiring up

Log return: rₜ = ln(Pₜ / Pₜ₋₁) · Annualised vol: σ_ann = σ_20d × √252

sql.js has no windowed STDDEV, so build the sample std-dev from three windowed sums (all valid as window functions):

σ = √[ (n·Σx² − (Σx)²) / (n·(n−1)) ]

Notice the n−1 in the denominator: with a 1-row window n = 1, so you divide by zero and vol comes back NULL. That is exactly what the broken placeholder does - your job is to widen the ruler to 20 rows.

Your Task

The query is fully wired: log returns in the first CTE, SMA and the vol algebra in the second. Only the window frame w is stubbed out to a single row (CURRENT ROW AND CURRENT ROW). Replace it with the real 20-day rolling frame - partition per ticker, order by date, and set ROWS BETWEEN 19 PRECEDING AND CURRENT ROW. Return one row per ticker at the most recent close.

Predict before you run: with the stub as-is, what value will vol_20_ann_pct show for every ticker - a number, a zero, or NULL? (Hint: a one-day window has n = 1, and the formula divides by n·(n−1).) Run the stub, confirm your guess, then fix the frame and watch five real vols appear.