gap_frequency

Run it yourself

This is the exact query behind the result above. Change a ticker, a date or a column and run it against the warehouse — no account, no key. The no-signup tier is smaller than the one this page was computed on; a query that reaches past it comes back saying which plan runs it.

WITH
    split_tickers AS
    (
        SELECT DISTINCT ticker
        FROM global_markets.stocks_splits
        WHERE execution_date >= '2025-10-01'
          AND execution_date <  '2026-08-01'
    ),
    px AS
    (
        SELECT
            ticker                AS symbol,
            date                  AS session,
            max(toFloat64(close)) AS px
        FROM global_markets.stocks_daily_aggs
        WHERE date >= '2025-10-01'
          AND date <  '2026-08-01'
          AND ticker NOT IN (SELECT ticker FROM split_tickers)
          AND ticker NOT IN ('SPCX')
        GROUP BY symbol, session
    ),
    iv_rows AS
    (
        SELECT
            g.symbol  AS symbol,
            g.session AS session,
            g.iv      AS iv
        FROM
        (
            SELECT
                underlying_symbol       AS symbol,
                toDate(date)            AS session,
                toFloat64(strike_price) AS strike,
                implied_volatility      AS iv
            FROM global_markets.options_greeks
            WHERE date >= '2025-10-01'
              AND date <  '2026-07-01'
              AND lower(toString(option_type)) IN ('c', 'call')
              AND days_to_expiry BETWEEN 20 AND 45
              AND implied_volatility > 0
        ) AS g
        INNER JOIN px AS p ON p.symbol = g.symbol AND p.session = g.session
        WHERE p.px > 10
          AND abs(g.strike / p.px - 1) < 0.05
    ),
    implied AS
    (
        SELECT
            symbol,
            iv_month,
            addMonths(iv_month, 1) AS forward_month,
            implied_vol_pct
        FROM
        (
            SELECT
                symbol,
                toStartOfMonth(session) AS iv_month,
                round(100 * avg(iv), 1) AS implied_vol_pct
            FROM iv_rows
            GROUP BY symbol, iv_month
            HAVING countDistinct(session) >= 15
               AND count() >= 150
        )
    ),
    daily_returns AS
    (
        SELECT
            symbol,
            arrayJoin(arrayMap((a, b) -> (tupleElement(a, 1), log(tupleElement(a, 2) / tupleElement(b, 2))),
                               arraySlice(series, 2),
                               arraySlice(series, 1, length(series) - 1))) AS ret
        FROM
        (
            SELECT
                symbol,
                arraySort(groupArray((session, px))) AS series
            FROM px
            WHERE symbol IN (SELECT symbol FROM implied)
            GROUP BY symbol
        )
    ),
    realized AS
    (
        SELECT
            symbol,
            toStartOfMonth(tupleElement(ret, 1))                         AS rv_month,
            round(100 * sqrt(252) * stddevSamp(tupleElement(ret, 2)), 1) AS realized_vol_pct
        FROM daily_returns
        GROUP BY symbol, rv_month
        HAVING count() >= 15
    )
SELECT
    formatDateTime(implied.iv_month, '%Y-%m') AS month,
    count()                                   AS names_measured,
    round(100 * countIf(implied.implied_vol_pct > realized.realized_vol_pct) / count(), 1) AS positive_gap_share_pct,
    round(quantileDeterministic(0.5)(implied.implied_vol_pct - realized.realized_vol_pct,
                                     cityHash64(implied.symbol)), 1) AS median_gap_pct
FROM implied
INNER JOIN realized ON realized.symbol = implied.symbol
                   AND realized.rv_month = implied.forward_month
GROUP BY month
ORDER BY month