How a new option chain widens: strikes and expirations traded

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
listings AS (
    SELECT
        ticker,
        min(listing_date) AS listed_on
    FROM global_markets.stocks_ipos
    WHERE listing_date >= '2024-01-01'
      AND listing_date < today()
      AND ticker NOT IN ('SPCX')
    GROUP BY ticker
),
debut AS (
    SELECT
        a.ticker                                                 AS symbol,
        min(a.date)                                              AS debut_date,
        argMin(toFloat64(a.close) * toFloat64(a.volume), a.date) AS debut_turnover
    FROM global_markets.stocks_daily_aggs AS a
    INNER JOIN listings AS l ON l.ticker = a.ticker
    WHERE a.date >= '2024-01-01'
      AND a.date >= l.listed_on
    GROUP BY a.ticker
),
cohort AS (
    SELECT
        symbol,
        debut_date
    FROM debut
    ORDER BY debut_turnover DESC
    LIMIT 40
),
first_option AS (
    SELECT
        g.underlying_symbol AS symbol,
        min(g.date)         AS option_date
    FROM global_markets.options_greeks AS g
    INNER JOIN cohort AS c ON c.symbol = g.underlying_symbol
    WHERE g.date >= '2024-01-01'
      AND g.volume > 0
      AND g.date >= c.debut_date
    GROUP BY g.underlying_symbol
),
daily_chain AS (
    SELECT
        g.underlying_symbol             AS symbol,
        g.date                          AS d,
        uniqExact(g.strike_price)       AS strikes,
        uniqExact(g.expiration_date)    AS expiries
    FROM global_markets.options_greeks AS g
    INNER JOIN first_option AS f ON f.symbol = g.underlying_symbol
    WHERE g.volume > 0
      AND g.date >= f.option_date
      AND dateDiff('day', f.option_date, g.date) < 84
    GROUP BY symbol, d
)
SELECT
    intDiv(dateDiff('day', f.option_date, c.d), 7)                                       AS weeks_since_first_option,
    toUInt32(round(quantileDeterministic(toFloat64(c.strikes), cityHash64(c.symbol))))   AS median_strikes_traded,
    toUInt32(round(quantileDeterministic(toFloat64(c.expiries), cityHash64(c.symbol))))  AS median_expirations_traded
FROM daily_chain AS c
INNER JOIN first_option AS f ON f.symbol = c.symbol
GROUP BY weeks_since_first_option
ORDER BY weeks_since_first_option