Weekly realised volatility and the weight it implied, Nov 2019 to Apr 2020

WITH
    px AS
    (
        SELECT
            date                  AS d,
            toFloat64(any(close)) AS c
        FROM global_markets.stocks_daily_aggs
        WHERE ticker = 'SPY'
          AND date >= '2019-09-01'
          AND date <  '2020-05-01'
        GROUP BY d
    ),
    px_sorted AS
    (
        SELECT arraySort(p -> p.1, groupArray((d, c))) AS pts
        FROM px
    ),
    rets AS
    (
        SELECT
            arrayMap(p -> p.1, arraySlice(pts, 2)) AS ds,
            arrayMap((a, b) -> log(b.2 / a.2),
                     arraySlice(pts, 1, length(pts) - 1),
                     arraySlice(pts, 2))           AS rs
        FROM px_sorted
    ),
    rolling AS
    (
        SELECT arrayJoin(arrayMap(i -> (ds[i],
                   arrayReduce('stddevSamp', arraySlice(rs, i - 20, 21)) * sqrt(252)),
                 range(21, length(rs) + 1))) AS pt
        FROM rets
    )
SELECT
    tupleElement(pt, 1)                                       AS date,
    formatDateTime(tupleElement(pt, 1), '%b %e, %Y')          AS week_label,
    round(tupleElement(pt, 2) * 100, 1)                       AS realised_vol_pct,
    round(100 * least(0.10 / tupleElement(pt, 2), 2.0), 1)    AS weight_pct
FROM rolling
WHERE date >= '2019-11-01'
  AND toDayOfWeek(date) = 5
ORDER BY date