How quickly the forty largest new listings got a traded option chain

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
),
ramp AS (
    SELECT
        a.ticker                                                 AS symbol,
        a.date                                                   AS d,
        row_number() OVER (PARTITION BY a.ticker ORDER BY a.date) AS session_no,
        toFloat64(a.volume) / 1e6                                AS shares_mm
    FROM global_markets.stocks_daily_aggs AS a
    INNER JOIN cohort AS c ON c.symbol = a.ticker
    WHERE a.date >= '2024-01-01'
      AND a.date >= c.debut_date
)
SELECT
    r.session_no AS session_number,
    round(100 * countIf(f.option_date >= toDate('2024-01-01') AND r.d >= f.option_date) / count(), 1) AS pct_with_listed_options,
    round(quantileDeterministic(r.shares_mm, cityHash64(r.symbol)), 2)                               AS median_share_volume_mm
FROM ramp AS r
LEFT JOIN first_option AS f ON f.symbol = r.symbol
WHERE r.session_no <= 20
GROUP BY session_number
ORDER BY session_number