MU by month: annualized realized volatility, average session range, biggest single-day move

SELECT
    toString(toStartOfMonth(et_date)) AS period_start,
    round(stddevSamp(log_ret) * sqrt(252) * 100, 1) AS realized_vol_ann_pct,
    round(100 * stddevSamp(log_ret) / max(stddevSamp(log_ret)) OVER (), 1) AS pct_of_peak_vol,
    round(avg(day_range_pct), 1) AS avg_day_range_pct,
    round(max(abs(day_move_pct)), 1) AS biggest_day_move_pct,
    count() AS return_days
FROM (
    SELECT et_date, day_range_pct,
        if(prev_close > 0, ln(close_usd / prev_close), NULL) AS log_ret,
        if(prev_close > 0, (close_usd / prev_close - 1) * 100, NULL) AS day_move_pct
    FROM (
        SELECT et_date, close_usd, day_range_pct,
            lagInFrame(close_usd) OVER (ORDER BY et_date ASC ROWS BETWEEN 1 PRECEDING AND CURRENT ROW) AS prev_close
        FROM (
            SELECT toDate(toTimeZone(window_start, 'America/New_York')) AS et_date,
                argMaxIf(toFloat64(close), window_start, (toHour(toTimeZone(window_start, 'America/New_York')) * 60 + toMinute(toTimeZone(window_start, 'America/New_York'))) BETWEEN 570 AND 959) AS close_usd,
                (maxIf(toFloat64(high), (toHour(toTimeZone(window_start, 'America/New_York')) * 60 + toMinute(toTimeZone(window_start, 'America/New_York'))) BETWEEN 570 AND 959) / minIf(toFloat64(low), (toHour(toTimeZone(window_start, 'America/New_York')) * 60 + toMinute(toTimeZone(window_start, 'America/New_York'))) BETWEEN 570 AND 959) - 1) * 100 AS day_range_pct
            FROM global_markets.delayed_stocks_minute_aggs
            WHERE ticker = 'MU'
              AND window_start >= toDateTime('2026-01-01 00:00:00') AND window_start < toDateTime('2026-07-01 00:00:00')
            GROUP BY et_date
        )
    )
)
WHERE isFinite(log_ret) AND log_ret IS NOT NULL
GROUP BY period_start
ORDER BY period_start