worst_gaps

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) >= 18
               AND count() >= 500
        )
    ),
    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
    concat(implied.symbol, ' ', formatDateTime(implied.forward_month, '%b %Y')) AS label,
    implied.implied_vol_pct                                                     AS implied_vol_pct,
    realized.realized_vol_pct                                                   AS realized_vol_pct,
    round(implied.implied_vol_pct - realized.realized_vol_pct, 1)               AS gap_pct
FROM implied
INNER JOIN realized ON realized.symbol = implied.symbol
                   AND realized.rv_month = implied.forward_month
WHERE realized.realized_vol_pct > implied.implied_vol_pct
ORDER BY gap_pct ASC
LIMIT 10