Write the SQL that returns, per symbol and per trading day, the 20 day rolling standard deviation of daily returns from a table of daily closes, and explain what changes if you write RANGE BETWEEN 19 PRECEDING AND CURRENT ROW instead of ROWS.

Write the SQL that returns, per symbol and per trading day, the 20 day rolling standard deviation of daily returns from a table of daily closes, and explain what changes if you write RANGE BETWEEN 19 PRECEDING AND CURRENT ROW instead of ROWS.

Approach: Build the return with a lagged close in one pass, wrap it in a second pass for the deviation, then ask what the frame clause is counting in each form.

Compute the return in one pass and the deviation in a second, framing on ROWS: SELECT symbol, d, stddev_samp(r) OVER (PARTITION BY symbol ORDER BY d ROWS BETWEEN 19 PRECEDING AND CURRENT ROW) FROM (SELECT symbol, d, close / lag(close) OVER (PARTITION BY symbol ORDER BY d) - 1 AS r FROM closes) t. The nesting is required because a window function cannot be an argument to another window function in the same select list. ROWS counts physical rows, so 19 preceding means 19 rows back in this symbol's own sorted history, which is what a 20 observation window means. RANGE counts values of the ORDER BY expression, so on a date column RANGE 19 PRECEDING means 19 calendar days, which returns a 14 observation window in a normal fortnight and fewer across a holiday, so the volatility estimate then moves with the calendar. Two further points: the first 19 rows per symbol are computed from fewer observations, so filter them with a count over the same frame, and if the convention is a zero mean estimator use sqrt(avg(r * r)) over the frame instead of stddev_samp.

Follow-up: How do you get the same rolling window when a symbol has gaps because it did not trade on some days?

Key concepts: lag window function, rows frame, range frame, partition by symbol.