Survivors-only average vs whole-cohort average, by starting year

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
    entry AS
    (
        SELECT
            ticker,
            toYear(date)                   AS cohort_start,
            argMin(toFloat64(close), date) AS entry_close
        FROM global_markets.stocks_daily_aggs
        WHERE toMonth(date) = 1
          AND date BETWEEN '2016-01-01' AND '2022-01-31'
          AND ticker NOT IN ('SPCX')
        GROUP BY ticker, cohort_start
        HAVING argMin(toFloat64(close), date) >= 5
           AND avg(volume) >= 250000
    ),
    outcome AS
    (
        SELECT
            ticker,
            argMax(toFloat64(close), date) AS final_close,
            max(date)                      AS last_bar
        FROM global_markets.stocks_daily_aggs
        WHERE date >= '2016-01-01'
        GROUP BY ticker
    )
SELECT
    toString(e.cohort_start)                                                             AS cohort_year,
    count()                                                                              AS cohort_size,
    countIf(o.last_bar < today() - 45)                                                   AS gone_count,
    round(100 * avgIf(o.final_close / e.entry_close - 1, o.last_bar >= today() - 45), 1) AS survivors_only_pct,
    round(100 * avg(o.final_close / e.entry_close - 1), 1)                               AS full_universe_pct,
    round(100 * (avgIf(o.final_close / e.entry_close - 1, o.last_bar >= today() - 45)
                 - avg(o.final_close / e.entry_close - 1)), 1)                           AS gap_pct
FROM entry AS e
INNER JOIN outcome AS o ON o.ticker = e.ticker
GROUP BY e.cohort_start
HAVING countIf(o.last_bar >= today() - 45) > 0
ORDER BY e.cohort_start