Brier score of the market's own stated probability, by underlying

WITH settle AS
(
    SELECT
        underlying_symbol                AS sym,
        date                             AS settle_date,
        any(toFloat64(underlying_close)) AS settle_px
    FROM global_markets.options_greeks
    WHERE underlying_symbol IN ('SPY', 'AAPL', 'MSFT', 'NVDA', 'KO', 'TSLA')
      AND date >= '2025-01-01'
      AND date <  '2026-08-01'
    GROUP BY sym, settle_date
),
scored AS
(
    SELECT
        g.underlying_symbol                             AS symbol,
        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.sym = g.underlying_symbol AND s.settle_date = g.expiration_date
    WHERE g.underlying_symbol IN ('SPY', 'AAPL', 'MSFT', 'NVDA', 'KO', 'TSLA')
      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 28 AND 35
      AND g.iv_converged = 1
      AND g.volume > 0
      AND abs(g.delta) > 0.02
      AND abs(g.delta) < 0.98
)
SELECT
    symbol,
    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,
    formatReadableQuantity(count())                                  AS graded_contracts
FROM scored
GROUP BY symbol
ORDER BY market_brier