3,256 answered market questions

WITH
    px AS
    (
        SELECT
            date                  AS d,
            toFloat64(any(close)) AS c
        FROM global_markets.stocks_daily_aggs
        WHERE ticker = 'SPY'
          AND date >= toStartOfMonth(subtractYears(today(), 6))
          AND date <  toStartOfMonth(today())
        GROUP BY d
    ),
    px_sorted AS
    (
        SELECT arraySort(p -> p.1, groupArray((d, c))) AS pts
        FROM px
    ),
    rets AS
    (
        SELECT arrayJoin(arrayFilter(x -> abs(x.2) < 0.4,
                   arrayMap((a, b) -> (b.1, log(b.2 / a.2)),
                            arraySlice(pts, 1, length(pts) - 1),
                            arraySlice(pts, 2)))) AS r
        FROM px_sorted
    )
SELECT
    toStartOfMonth(tupleElement(r, 1))                          AS month,
    formatDateTime(toStartOfMonth(tupleElement(r, 1)), '%b %Y') AS month_label,
    round(stddevSamp(tupleElement(r, 2)) * sqrt(252) * 100, 1)  AS realised_vol_pct,
    10                                                          AS target_vol_pct
FROM rets
GROUP BY month, month_label
ORDER BY month