Average daily range of recent listings, by age since listing

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 ipo AS
(
    SELECT
        ticker,
        min(listing_date) AS listed
    FROM global_markets.stocks_ipos
    WHERE listing_date >= today() - 900
      AND listing_date <= today() - 120
      AND ticker NOT IN ('SPCX')
    GROUP BY ticker
)
SELECT
    multiIf(age <=  7, 'Days 1 to 7',
            age <= 14, 'Days 8 to 14',
            age <= 30, 'Days 15 to 30',
            age <= 60, 'Days 31 to 60',
                       'Days 61 to 90')  AS days_since_listing,
    round(avg(range_pct), 2)             AS avg_daily_range_pct,
    uniqExact(ticker)                    AS cohort_size
FROM
(
    SELECT
        a.ticker                                             AS ticker,
        dateDiff('day', i.listed, a.date)                    AS age,
        100 * toFloat64(a.high - a.low) / toFloat64(a.close) AS range_pct
    FROM global_markets.stocks_daily_aggs AS a
    INNER JOIN ipo AS i ON i.ticker = a.ticker
    WHERE a.date >= today() - 1000
      AND a.volume > 0
      AND a.close > 0
)
WHERE age >= 0
  AND age <= 90
GROUP BY days_since_listing
ORDER BY min(age)