מתי מתחיל מסחר באופציות לאחר הנפקת IPO?

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

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

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

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,
        max(g.days_to_expiry) AS longest_dte
    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
),
weekly AS (
    SELECT
        c.symbol                                       AS symbol,
        intDiv(dateDiff('day', f.option_date, c.d), 7) AS wk,
        max(c.longest_dte)                             AS longest_dte
    FROM daily_chain AS c
    INNER JOIN first_option AS f ON f.symbol = c.symbol
    GROUP BY symbol, wk
)
SELECT
    wk                                                                                    AS weeks_since_first_option,
    round(100 * countIf(longest_dte > 365) / count(), 1)                                  AS pct_with_leaps,
    toUInt32(round(quantileDeterministic(toFloat64(longest_dte), cityHash64(symbol))))    AS median_longest_dte
FROM weekly
GROUP BY wk
ORDER BY wk