3,256 answered market questions

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