Trading sessions from first equity print to first listed option print

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
),
first_option AS (
    SELECT
        g.underlying_symbol AS symbol,
        min(g.date)         AS option_date
    FROM global_markets.options_greeks AS g
    INNER JOIN debut AS d ON d.symbol = g.underlying_symbol
    WHERE g.date >= '2024-01-01'
      AND g.volume > 0
      AND g.date >= d.debut_date
    GROUP BY g.underlying_symbol
),
paired AS (
    SELECT
        d.symbol         AS symbol,
        d.debut_date     AS debut_date,
        f.option_date    AS option_date,
        d.debut_turnover AS debut_turnover
    FROM debut AS d
    INNER JOIN first_option AS f ON f.symbol = d.symbol
    ORDER BY debut_turnover DESC
    LIMIT 12
),
sessions AS (
    SELECT DISTINCT date AS d
    FROM global_markets.stocks_daily_aggs
    WHERE ticker = 'SPY'
      AND date >= '2024-01-01'
)
SELECT
    p.symbol AS symbol,
    concat(monthName(p.debut_date), ' ', toString(toDayOfMonth(p.debut_date)), ', ', toString(toYear(p.debut_date)))    AS equity_debut,
    concat(monthName(p.option_date), ' ', toString(toDayOfMonth(p.option_date)), ', ', toString(toYear(p.option_date))) AS option_debut,
    countIf(s.d > p.debut_date AND s.d <= p.option_date) AS option_listing_gap
FROM paired AS p
CROSS JOIN sessions AS s
GROUP BY symbol, equity_debut, option_debut
ORDER BY option_listing_gap, symbol