The market's Brier score across expiry horizons, SPY

WITH settle AS
(
    SELECT
        date                             AS settle_date,
        any(toFloat64(underlying_close)) AS settle_px
    FROM global_markets.options_greeks
    WHERE underlying_symbol = 'SPY'
      AND date >= '2025-01-01'
      AND date <  '2026-08-01'
    GROUP BY date
),
scored AS
(
    SELECT
        multiIf(g.days_to_expiry <=   7, 1,
                g.days_to_expiry <=  14, 2,
                g.days_to_expiry <=  30, 3,
                g.days_to_expiry <=  60, 4,
                g.days_to_expiry <= 120, 5,
                6)                                      AS horizon_rank,
        abs(toFloat64(g.delta))                         AS stated,
        startsWith(lower(toString(g.option_type)), 'c') AS is_call,
        if(is_call,
           s.settle_px > toFloat64(g.strike_price),
           s.settle_px < toFloat64(g.strike_price))     AS finished_itm
    FROM global_markets.options_greeks AS g
    INNER JOIN settle AS s ON s.settle_date = g.expiration_date
    WHERE g.underlying_symbol = 'SPY'
      AND g.date >= '2025-01-01'
      AND g.date <  '2026-06-01'
      AND g.expiration_date <= '2026-07-31'
      AND g.days_to_expiry BETWEEN 1 AND 250
      AND g.iv_converged = 1
      AND g.volume > 0
      AND abs(g.delta) > 0.02
      AND abs(g.delta) < 0.98
)
SELECT
    multiIf(horizon_rank = 1, '1 to 7 days',
            horizon_rank = 2, '8 to 14 days',
            horizon_rank = 3, '15 to 30 days',
            horizon_rank = 4, '31 to 60 days',
            horizon_rank = 5, '61 to 120 days',
            '121 to 250 days')                                       AS horizon,
    round(avg((stated - finished_itm) * (stated - finished_itm)), 4) AS market_brier,
    round(avg((0.5 - finished_itm) * (0.5 - finished_itm)), 4)       AS coin_flip_brier,
    count()                                                          AS sample_size
FROM scored
GROUP BY horizon_rank
ORDER BY horizon_rank