3,256 answered market questions

SELECT
    toYear(d)                                                AS year,
    round(stddevSamp(log(c / prev_c)) * sqrt(252) * 100, 1)  AS realized_vol_pct,
    round(max(abs(c / prev_c - 1)) * 100, 1)                 AS largest_move_pct,
    countIf(abs(c / prev_c - 1) >= 0.02)                     AS moves_over_2pct
FROM
(
    SELECT
        d,
        c,
        lagInFrame(c, 1) OVER (ORDER BY d ASC ROWS BETWEEN 1 PRECEDING AND CURRENT ROW) AS prev_c
    FROM
    (
        SELECT
            date                    AS d,
            toFloat64(any(close))   AS c
        FROM global_markets.stocks_daily_aggs
        WHERE ticker = 'SPY'
          AND date >= '2003-12-01'
        GROUP BY d
    )
)
WHERE d >= '2004-01-01'
  AND prev_c > 0
GROUP BY year
ORDER BY year